CODEWITHSUNDEEP
sqlsql-server-2008t-sql
PeterRing
PeterRing

Reputation: 1797

Rotate one row with column names

I have the following problem: I have one row of data, and I would like to rotate it with then names of the colums.

Collnames:     a    b   c    d
               0   NULL 1    0

And i woluld like to have The folowing:

Collnames  Data
a          0
b          Null
c          1
d          0

I made a solution for this, but its using dynamic SQL with a cursor, and I'm not proud of it. Is there a more elegat way to solve this problem?

Thx

Upvotes: 2

Views: 75

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

This is an UNPIVOT though the built in operator won't preserve b as the value is NULL so you can do it this way.

SELECT ColNames,Data 
FROM   YourTable 
       CROSS APPLY ( VALUES ('a', a), 
                            ('b', b), 
                            ('c', c), 
                            ('d', d)) V(ColNames, Data)

SQL Fiddle

Upvotes: 4

Related Questions