2

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
Improve this question
2
  • Your output is not at all clear. Do you want the literal values 'C2' etc. If you can explain your output clearly we can help. Commented Aug 28, 2015 at 16:39
  • @SeanLange I want the column name in 'Columns'. I will edit the OP to clarify. Thanks Commented Aug 28, 2015 at 16:56

2 Answers 2

Reset to default
1

Query

SELECT Tax 
      ,ptype
      ,[Columns]
      ,Value 
   FROM @t
     UNPIVOT (Value FOR Columns IN ( C1 , C2 , C3 ))up

Result Set

╔═══════╦═══════╦═════════╦═══════╗
║  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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I will do this using CROSS APPLY and table valued constructor to unpivot the data

SELECT tax, 
       ptype, 
       columns, 
       value 
FROM   @t 
       CROSS apply (Select 'c1',c1 
                    UNION ALL 
                    Select 'c2',c2 
                    UNION ALL
                    Select 'c3',c3 ) cs(columns, value) 
WHERE  ptype IS NOT NULL 
       AND columns IS NOT NULL 
       AND value IS NOT NULL

SQL FIDDLE DEMO

Improve this answer

4 Comments

Thanks @Indian but I am on SQL serve 2005 and I believe CROSS apply does not work
@user176047 cross apply was introduces in 2005 so it should work as long as your database compatibility level is set to 90.
@user176047 - Ali is correct. Cross apply is introduced in 2005. But the problem is table valued constructor which is introduced in 2008. Updated the code check now. This is why you should add proper tags to your question
I find it much easier doing it with UNPIVOT since the syntax was introduced for situations like this one ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.