Pivot Question
-
I have been looking at using PIVOT to convert rows to columns but I don't need nor want an aggregated column. To get around this I've used the following but there must be a better solution:
create table #test ([Plan] varchar(10), Setting varchar(10), [Value] varchar(10))insert into #test ([Plan], Setting, [Value])
values ('A','Colour','Red')
insert into #test ([Plan], Setting, [Value])
values ('A','Size','10')
insert into #test ([Plan], Setting, [Value])
values ('A','Height','5')
insert into #test ([Plan], Setting, [Value])
values ('B','Colour','Blue')
insert into #test ([Plan], Setting, [Value])
values ('B','Size','11')
insert into #test ([Plan], Setting, [Value])
values ('B','Height','6')
insert into #test ([Plan], Setting, [Value])
values ('C','Colour','Green')
insert into #test ([Plan], Setting, [Value])
values ('C','Size','20')
insert into #test ([Plan], Setting, [Value])
values ('C','Height','10')
select distinct a.[Plan], b.Value as Colour, c.Value as [Size], d.Value as Height
from #test a
join #test b on b.[Plan] = a.[Plan] and b.Setting = 'Colour'
join #test c on c.[Plan] = a.[Plan] and c.Setting = 'Size'
join #test d on d.[Plan] = a.[Plan] and d.Setting = 'Height'
Any help would be appreciated.
Thanks
David
-
I tend to use the "max ... group by" pattern
select [Plan],max(case when Setting='Colour' then [Value] end) as Colour,
max(case when Setting='Size' then [Value] end) as Size,
max(case when Setting='Height' then [Value] end) as Height
from #test
group by [Plan]
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply