Reputation: 827
This seems like a straight-forward scenario but I'm stumped ...
I have a table with the following data:
IsGood Reason
------ ------
1 Good One
1 Good Two
0 Bad One
0 Bad Two
0 Bad Three
And I want to query it to pivot the data horizontally, like this:
GoodReason BadReason
---------- ---------
Good One Bad One
Good Two Bad Three
NULL Bad Two
However the results come out like this:
GoodReason BadReason
---------- ---------
NULL Bad One
NULL Bad Three
NULL Bad Two
Good One NULL
Good Two NULL
create table #reasons
(
IsGood bit,
Reason nvarchar(20)
)
insert into #reasons
( IsGood, Reason )
values ( 1, 'Good One' )
, ( 1, 'Good Two' )
, ( 0, 'Bad One' )
, ( 0, 'Bad Two' )
, ( 0, 'Bad Three' )
select * from #reasons
select 'Attempt #1'
, case when r.IsGood = 1 then r.Reason else null end GoodReason
, case when r.IsGood = 0 then r.Reason else null end BadReason
from
#reasons r
group by r.IsGood, r.Reason
select 'Attempt #2'
, max(case when r.IsGood = 1 then r.Reason else null end) GoodReason
, max(case when r.IsGood = 0 then r.Reason else null end) BadReason
from
#reasons r
group by r.IsGood, r.Reason
drop table #reasons
go
The good/bad reasons are driven from the UI and can change, be added to, etc so I've ruled out a PIVOT approach - happy to educated otherwise!.
Repo also available at - http://sqlfiddle.com/#!18/9ebc3/2
How to I get the results without the null values?
Many thanks,
Franz.
Upvotes: 1
Views: 161
Reputation: 50173
Use ROW_NUMBER()
function to generate the sequence number based on IsGood and use in GROUP BY
clause for the derived table
select
max(case when IsGood = 1 then Reason end) GoodReason,
max(case when IsGood = 0 then Reason end) BadReason
from
(
select *,
row_number() over (partition by IsGood order by IsGood) Seq
from reasons
)a
group by Seq
Upvotes: 2