
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)
RecordID | TypeA | TypeB | TypeC | TypeD |
1 | T | T | T | T |
2 | T | F | T | F |
3 | F | F | T | T |
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!
Subscribe To Our Newsletter
Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!
You have Successfully Subscribed!