Select table column names as values

Question

Given a SQL table with an arbitrary number of records (X) and an arbitrary number of columns (Y)

RecordID	Column1	Column 2	Column 3	...	Column Y
1	Value11	Value12	Value13	...	Value1Y
2	Value21	Value22	Value23	...	Value2Y
...	...	...	...	...	...
X	ValueX1	ValueX2	ValueX3	...	ValueXY

Is it possible to construct a query that reduces the data to a simple three-column lookup of ID, column name and value, as follows :

RecordID	ColumnName	Value
1	Column1	Value11
1	Column2	Value12
1	Column3	Value13
...	...	...
1	ColumnY	Value1Y
2	Column1	Value21
2	Column2	Value22
2	Column3	Value23
...	...	...
2	ColumnY	Value2Y
...	...	...
X	Column1	ValueX1
X	Column2	ValueX2
X	Column3	ValueX3
...	...	...
X	ColumnY	ValueXY

Conservation of the data type is obviously not a concern, a simple text representation on each case would be sufficient?

(I'm sure this is answered elsewhere but I don't know what this would be "called" so I'm having trouble finding out how to achieve it...)

This is called "unpivoting" a search of this will give you a wealth of solutions. — Thom A, Sep 12 '22 at 14:39

score 3 · Accepted Answer · answered Sep 12 '22 at 14:44

3

UNPIVOT is more performant, but here is an option that will dynamically unpivot your data without actually using dynamic SQL, and you don't have to specify all the columns nor worry about datatypes/conversions.

Select A.RecordID
      ,B.* 
 From  YourTable A
 Cross Apply  (
                 Select [Key]
                       ,Value
                 From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
                 Where [Key] not in ('RecordID','OtherColumns','ToExclude')
              ) B

answered Sep 12 '22 at 14:44

John Cappelletti

79,615
7
44
66

Thanks @John, I came across one of your previous answers here : https://stackoverflow.com/questions/54747723/unpivoting-with-column-headers-single-row after reading Larnu's original comment, is there any reason or otherwise to choose one over the other? – Alan O'Brien Sep 12 '22 at 14:49
@AlanO'Brien This answer has the benefit that it works dynamically (sort of, because in a view or stored procedure you still need `sp_refreshsqlmodule` if there are changes to the base table design). But it's going to be much much slower than unpivoting using either `UNPIVOT` or `CROSS APPLY` – Charlieface Sep 12 '22 at 14:51
@AlanO'Brien Performance over Flexibility. You need to decide which is more important. That said, you may be surprised with the performance of my approach. – John Cappelletti Sep 12 '22 at 14:51

Select table column names as values

1 Answers1

Linked