Search code examples
sqlsql-servert-sqlpivotunpivot

SQL Converting Columns to Rows

I want to perform the following conversion:

Seniority    Price   Rating
---------------------------
   1          P1      R1
   2          P2      R2
   3          P3      R3

to

Value RowId ColId
------------------
 1     0      0
 P1    0      1
 R1    0      2
 2     1      0 
 P2    1      1
 R2    1      2
 3     2      0 
 P3    2      1
 R3    2      2

If possible I would like to preserve the field name as well in the transformed table, i.e. all rows with rowid=0 will have Seniority added as a field, rowid=1 will have Price, so on.

Solution

  • Here is an option using JSON if 2016+ An XML version is available for older version.

    Example

    Declare @YourTable Table ([Seniority] varchar(50),[Price] varchar(50),[Rating] varchar(50))  
Insert Into @YourTable Values 
 (1,'P1','R1')
,(2,'P2','R2')
,(3,'P3','R3')


Select B.Value
      ,A.RowID
      ,B.ColID
 From  (Select *
              ,RowID = row_number() over (order by (select null))  - 1
         From  @YourTable    --<<< Replace with virtually any table.
       ) A
 Cross Apply (
                Select *
                      ,ColID = row_number() over (order by (select null)) - 1
                 From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES ) ) 
                 Where [Key] not in ('RowID')
             ) B

    Returns

    enter image description here

    EDIT - Just for Fun, the XML version

    Select C.Value
      ,A.RowID
      ,C.ColID
 From  (Select *
              ,RowID = row_number() over (order by (select null))  - 1
         From  @YourTable 
       ) A
 Cross Apply ( values ( convert(xml,(Select A.* for XML RAW)) ) ) B(XMLData)
 Cross Apply (
                Select ColID = row_number() over( order by (select null)) - 1
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)', 'varchar(100)') not in ('RowID')
             ) C

    EDIT -- XML Version to ALLOW NULL values

    Declare @YourTable Table ([Seniority] varchar(50),[Price] varchar(50),[Rating] varchar(50))  
Insert Into @YourTable Values 
 (1,'P1','R1')
,(2,'P2','R2')
,(3,NULL,'R3')   -- Forced a NULL value


Select C.Value
      ,A.RowID
      ,C.ColID
 From  (Select *
              ,RowID = row_number() over (order by (select null))  - 1
         From  @YourTable 
       ) A
 Cross Apply ( values ( convert(xml,(Select A.* for XML RAW,ELEMENTS XSINIL)) ) ) B(XMLData)
 Cross Apply (
                Select Item  = attr.value('local-name(.)','varchar(100)')
                      ,Value = attr.value('.','varchar(max)') 
                      ,ColID = row_number() over (order by (select null)) - 1
                 From  XMLData.nodes('/row')  as C1(nd)
                 Cross Apply C1.nd.nodes('./*') as C2(attr)
                 Where attr.value('local-name(.)','varchar(100)') not in ('RowID')
             ) C

    Returns

    Value   RowID   ColID
1       0       0
P1      0       1
R1      0       2
0       0       3
2       1       0
P2      1       1
R2      1       2
1       1       3
3       2       0
        2       1  --<< Notice NULLs return as Empty Strings
R3      2       2
2       2       3