How to dynamically transpose some columns to rows if the columns I want to convert to rows all start with a prefix of 'c' in the column name. I have a table as follows
DECLARE @t codes
(
Tax CHAR(5),
ptype CHAR(2),
c1 CHAR(1),
c2 char(1),
c3 char(1)
)
insert into @t (tax, ptype, c1, c2, c3) values ('AAAAA','10',Null, 1,2)
insert into @t (tax, ptype, c1, c2, c3) values ('BBBBB','21',3, 1,NULL)
insert into @t (tax, ptype, c1, c2, c3) values ('ZZZZZ','1',NULL, NULL, 2)
insert into @t (tax, ptype, c1, c2, c3) values ('CCCCC',NULL,1,3,4)
insert into @t (tax, ptype, c1, c2, c3) values ('YYYYY','4',NULL, NULL, NULL)
insert into @t (tax, ptype, c1, c2, c3) values ('DDDDD','8',2,5,6)
How do I output the below where ptype is not 'NULL' and when c1,c2,c3 are not 'NULL' with C1,C2,C3 values sorted ascending?
Tax ptype Columns value
----- ----- ------- -----
AAAAA 10 c2 1
AAAAA 10 c3 2
BBBBB 21 c2 1
BBBBB 21 c1 3
ZZZZZ 1 c3 2
DDDDD 8 c1 2
DDDDD 8 c2 5
DDDDD 8 c3 6
SELECT Tax
,ptype
,[Columns]
,Value
FROM @t
UNPIVOT (Value FOR Columns IN ( C1 , C2 , C3 ))up
╔═══════╦═══════╦═════════╦═══════╗
║ Tax ║ ptype ║ Columns ║ Value ║
╠═══════╬═══════╬═════════╬═══════╣
║ AAAAA ║ 10 ║ c2 ║ 1 ║
║ AAAAA ║ 10 ║ c3 ║ 2 ║
║ BBBBB ║ 21 ║ c1 ║ 3 ║
║ BBBBB ║ 21 ║ c2 ║ 1 ║
║ ZZZZZ ║ 1 ║ c3 ║ 2 ║
║ CCCCC ║ NULL ║ c1 ║ 1 ║
║ CCCCC ║ NULL ║ c2 ║ 3 ║
║ CCCCC ║ NULL ║ c3 ║ 4 ║
║ DDDDD ║ 8 ║ c1 ║ 2 ║
║ DDDDD ║ 8 ║ c2 ║ 5 ║
║ DDDDD ║ 8 ║ c3 ║ 6 ║
╚═══════╩═══════╩═════════╩═══════╝
If you want to eliminate nulls from the result set just add where clause to the above query
WHERE [Columname] IS NOT NULL