CODEWITHSUNDEEP
sqlsql-servert-sql
toepoke.co.uk
toepoke.co.uk

Reputation: 827

Remove nulls in pivot query

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

Answers (1)

Yogesh Sharma
Yogesh Sharma

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

Related Questions