Pivoting data in SQL (Part 2)

In the past, I’ve written about pivoting data in SQL, and I wanted to post a quick follow-up showing basically how to rotate the data back into a single column.

My source data consists of a recordID field, then 5 different boolean fields representing a different type value:

RecordID (Varchar)

TypeA (Boolean)

TypeB (Boolean)

TypeC (Boolean)

TypeD (Boolean)

TypeE (Boolean)

I would like a view that returns two columns:  A RecordID, and a Type value.  In order to do this, I simply write a statement  for each type, then union those statements together in a view.

My statement looks like this:

create view vTemp as
     select recordid, [Type] = case TypeA when ‘T’ then ‘A’ End
     from datatable
     where case TypeA when ‘T’ then ‘A’ End is not null
union all
     select recordid, [Type] = case TypeB when ‘T’ then ‘B’ End
     from datatable
     where case TypeB when ‘T’ then ‘B’ End is not null
union all
     select recordid, [Type] = case TypeC when ‘T’ then ‘C’ End
     from datatable
     where case TypeC when ‘T’ then ‘C’ End is not null
union all
     select recordid, [Type] = case TypeD when ‘T’ then ‘D’ End
     from datatable
     where case TypeD when ‘T’ then ‘D’ End is not null
union all
     select recordid, [Type] = case TypeE when ‘T’ then ‘E’ End
     from datatable
     where case TypeE when ‘T’ then ‘E’ End is not null

This will create a view containing a single row for each type:

recordid    Type
1    A
1    B
1    D
1    E
2    B
2    D
2    E
3    D

That’s all there is to it! Just a simple example, but I find myself having to do this time and again.  I hope you find this helpful as well.

Thanks for reading!