Select table column names as values

Alan O'Brien 0
Viewed 45

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...)

sql-server sql
1 Answers

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
John Cappelletti0
Related