0

Say I have a table with a number of columns that is unknown to me, but only a single row:

A B C D E F G...n
1 2 3 4 5 6 7...x

And I would like to pivot it into a table of LABEL:VALUE pairs:

LABEL    VALUE 
 A         1  
 B         2  
 C         3  
...       ... 
 n         x

This seems like it should be a regular pivot (I think...sorry, a bit of a n00b to SQL), but don't I need to know the names of the columns that I wish to pivot, preventing me from pivoting an unknown number of columns? Is there a simple way to accomplish this?

Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
Kenneth Tomplait
  • 1
  • 1
  • Do you know the possible _names_ of the columns even if you don't know how many there will be? – D Stanley Feb 26 '15 at 21:44
  • D Stanley: I could swing the names, but not the quantity.

    xQbert: That does look close! Thank you, I didn't see that one when I was searching to post this question. I'll check it out!     – Kenneth Tomplait Feb 26 '15 at 21:50
  • @xQbert That wouldn't be a correct duplicate, this is an unpivot not a pivot. – Taryn Feb 26 '15 at 23:51
  • @KennethTomplait You need to look for unpivot solutions, which convert multiple columns into rows. This is not a pivot. See this - http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows – Taryn Feb 26 '15 at 23:52
  • Thanks, guys, and sorry for the duplicate. A friend showed me how I was screwing up, and here is the solution in case anyone stumbles on this one: DECLARE TEST TABLE ( THING1 VARCHAR (MAX), THING2 VARCHAR (MAX), THING3 VARCHAR (MAX), THING4 VARCHAR (MAX) ) INSERT INTO TEST SELECT 'A', 'B', 'C', 'D' SELECT * FROM TEST SELECT NAME, VALUE FROM ( SELECT [THING1], [THING2], [THING3], [THING4] FROM TEST ) AS UP UNPIVOT ( --NAME --FOR details in ([THING1], [THING2], [THING3], [THING4]) VALUE FOR NAME IN ([THING1], [THING2], [THING3], [THING4]) ) u; (Ampersands removed) – Kenneth Tomplait Feb 27 '15 at 00:04

0 Answers0