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…)
>Solution :
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