April 2, 2009 at 12:00 pm
Hi,
I can´t find any solution for my problem that works well and don´t need a cursor.
My problem is that I want each row to have special set of columns but that are sorted by the values in the columns.
I try to give an example.
Lets say I have 4 columns with numeric values.
A B C D
1 2 5 4
3 5 6 1
The result I want for each row is:
C D B A
5 4 2 1
C B A D
6 5 3 1
The reason is that I want to add a column in the table that show the columns with the right name an value in the right order.
How do I do this the best way?
Thanks
April 2, 2009 at 12:28 pm
You can kind of do that with Case statements.
The big question is, what do you want to do with ties? What if A = B?
Here's a sample with Case statements:
create table #T (
ID int identity primary key,
A int,
B int,
C int,
D int);
insert into #T (A, B, C, D)
select 1,2,3,4 union all
select 8,7,6,5;
select 1 as Seq, ID,
case
when a > b and a > c and a > d then 'A'
when b > a and b > c and b > d then 'B'
when c > b and c > a and c > d then 'C'
when d > b and d > c and d > a then 'D'
end as Col1,
case
when a > b and a > c and a < d then 'A'
when a d then 'A'
when a > b and a d then 'A'
when b d then 'B'
when b > a and b d then 'B'
when b > a and b > c and b < d then 'B'
when c d then 'C'
when c > b and c d then 'C'
when c > b and c > a and c < d then 'C'
when d a then 'D'
when d > b and d a then 'D'
when d > b and d > c and d < a then 'D'
end as Col2,
case
when a d then 'A'
when a > b and a < c and a < d then 'A'
when a c and a < d then 'A'
when b d then 'B'
when b > a and b < c and b < d then 'B'
when b c and b < d then 'B'
when c d then 'C'
when c > b and c < a and c < d then 'C'
when c a and c < d then 'C'
when d a then 'D'
when d > b and d < c and d < a then 'D'
when d c and d < a then 'D'
end as Col3,
case
when a < b and a < c and a < d then 'A'
when b < a and b < c and b < d then 'B'
when c < b and c < a and c < d then 'C'
when d < b and d < c and d < a then 'D'
end as Col4
from #T
union all
select 2, ID,
case
when a > b and a > c and a > d then cast(A as varchar(10))
when b > a and b > c and b > d then cast(B as varchar(10))
when c > b and c > a and c > d then cast(C as varchar(10))
when d > b and d > c and d > a then cast(D as varchar(10))
end as Col1,
case
when a > b and a > c and a < d then cast(A as varchar(10))
when a d then cast(A as varchar(10))
when a > b and a d then cast(A as varchar(10))
when b d then cast(B as varchar(10))
when b > a and b d then cast(B as varchar(10))
when b > a and b > c and b < d then cast(B as varchar(10))
when c d then cast(C as varchar(10))
when c > b and c d then cast(C as varchar(10))
when c > b and c > a and c < d then cast(C as varchar(10))
when d a then cast(D as varchar(10))
when d > b and d a then cast(D as varchar(10))
when d > b and d > c and d < a then cast(D as varchar(10))
end as Col2,
case
when a d then cast(A as varchar(10))
when a > b and a < c and a < d then cast(A as varchar(10))
when a c and a < d then cast(A as varchar(10))
when b d then cast(B as varchar(10))
when b > a and b < c and b < d then cast(B as varchar(10))
when b c and b < d then cast(B as varchar(10))
when c d then cast(C as varchar(10))
when c > b and c < a and c < d then cast(C as varchar(10))
when c a and c < d then cast(C as varchar(10))
when d a then cast(D as varchar(10))
when d > b and d < c and d < a then cast(D as varchar(10))
when d c and d < a then cast(D as varchar(10))
end as Col3,
case
when a < b and a < c and a < d then cast(A as varchar(10))
when b < a and b < c and b < d then cast(B as varchar(10))
when c < b and c < a and c < d then cast(C as varchar(10))
when d < b and d < c and d < a then cast(D as varchar(10))
end as Col4
from #T
order by ID, Seq;
It's as close as I can think of to what you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 12:32 pm
Thanks, I was thinking of this idea aswell but I don´t seems so elegant. But it will proberly work.
Ha actually have up to 10 values that I need to recompute and I´m afraid that the Case element gets to extreme.
April 2, 2009 at 3:17 pm
Maybe this solution is a little easier to expand to 10 columns...
What needs to be done is to add one SELECT statement to #t1 and two more CASE per addtl. column to the output statement.
It still doesn't look as nice at it would have with SS2K5.... 😀
-- prepare initial data
-- note: primary key [row] added
create table #t(
row int identity(1,1),
A int,
B int,
C int,
D int)
insert into #t(A,B,C,D)
select 1, 2, 5, 4 union all
select 3, 5, 6, 1
------------------------------
-- temporary table to reorder rows
-- Note: required for SQL2000, SS2K5 would allow using ROW_NUMBER()
create table #t2 (
id int identity(1,1),
row int,
col char(1),
val int)
-- reorder rows by Values Desc
insert into #t2(row, col,val)
select * from
(select row,'A' as col,a as val from #t union all
select row,'B'as col,b as val from #t union all
select row,'C'as col,c as val from #t union all
select row,'D'as col,d as val from #t) as a
order by row, val desc
-- show result
select * from
(select
row,
max(Case when id % 4= 1 then col else '' end)
+ ',' + max(Case when id % 4= 2 then col else '' end)
+ ',' + max(Case when id % 4= 3 then col else '' end)
+ ',' + max(Case when id % 4= 0 then col else '' end) as Col
from #t2
group by row
union all
select
row
, max(Case when id % 4= 1 then cast(val as char(1)) else '' end)
+ ',' + max(Case when id % 4= 2 then cast(val as char(1)) else '' end)
+ ',' + max(Case when id % 4= 3 then cast(val as char(1)) else '' end)
+ ',' + max(Case when id % 4= 0 then cast(val as char(1)) else '' end) as result
from #t2
group by row) as t
order by row, col desc
Edit: first post had addtl. temp table that turned out not to be required
April 2, 2009 at 4:20 pm
I think that a better question is "Why would you want to do this?"
The importance of this question that you are implying that the columns are interchangeable and that is not how a relational table is supposed to be. Rows are interchangeable, columns are not. If yours are, then your best approach is to redesign the table(s) to fix this:
CREATE Table #r(
OldRowID int,
OldColName char(1),
Val int
primary key (OldRowID, OldColName)
)
Now your problem is easy to solve.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 5:02 pm
@Barry:
Since your table #r is similar to table #t2 in my previous post I'm wondering if there is an even easier solution than mine to get the output the OP requested (regardless if there is a need to do it in the first place...). What did I miss?
April 2, 2009 at 5:26 pm
Hmm, I confess that I had missed your second table there.
And now I see that we are including the column headers as extra rows in the output. That does make it more complicated than I thought. Hmm, let me see...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 5:28 pm
Oops, and I see now that this is for SQL 2000. Now I would say that your approach is the best... I must be getting tired...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 9:03 pm
I guess what OP really needs is a comma (or other delimiter) separated list of values for some report.
If that's the case then OP needs to follow Barry's advice, normalize the data and then use "concatenation function" to return set of lists.
_____________
Code for TallyGenerator
April 2, 2009 at 11:06 pm
Thanks for all the advices, I´m actually new to this forum and it´s actually on a Sql server 2005 I´m trying to apply this. Does anyone have a easier solution then.
Regards
Mattias
April 2, 2009 at 11:17 pm
m.wardell (4/2/2009)
Thanks for all the advices, I´m actually new to this forum and it´s actually on a Sql server 2005 I´m trying to apply this. Does anyone have a easier solution then.Regards
Mattias
Mattias, was my assumption any close to the real situation?
_____________
Code for TallyGenerator
April 3, 2009 at 12:15 am
Sergiy (4/2/2009)
I guess what OP really needs is a comma (or other delimiter) separated list of values for some report.
If that's the case, isn't it exactly what the code I posted above will return?
Result:
rowcol
1C,D,B,A
15,4,2,1
2C,B,A,D
26,5,3,1
Since it's on SS2K5, the temp table is no longer required an can be replaced with a CTE together with ROW_NUMBER function.
April 3, 2009 at 12:53 am
Thanks Lutz, it givs me the resutl I was hoping for. I would love it if you showed me how to convert it for SQL server 2005.
Thanks again Lutz, I try to read better the next time.
Mattias
April 5, 2009 at 1:36 pm
Hi Matthias,
I'm sorry for not responding earlier but I just returned from a weekend trip.
The only modification I can see with SS2K5 is the replacement of the temp table with a CTE.
Inside that CTE the Row_Number function is used to bring the columns in the requested order.
The final select statement is unchanged since I couldn't find another way to display the results as requested.
Eliminate the temp table by replacing the code between
--- Note: required for SQL2000, SS2K5 would allow using ROW_NUMBER() and --- show result with
;with #t2(id,row,col,val) as
(
select
row_number() over (partition by row order by row,val desc),
row,col,val from
(select row,'A' as col,a as val from #t union all
select row,'B'as col,b as val from #t union all
select row,'C'as col,c as val from #t union all
select row,'D'as col,d as val from #t) as a
) The result will be identical:
-- result
rowCol
1C,D,B,A
15,4,2,1
2C,B,A,D
26,5,3,1
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply