SQL Converting Columns to Rows

Question

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.

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2020-06-30 19:00:08 +00:00
Commented Jun 30, 2020 at 19:00 — Gordon Linoff
– Gordon Linoff, Commented Jun 30, 2020 at 19:00

John Cappelletti · Accepted Answer · 2020-07-01 12:11:26Z

2

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

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

edited Jul 1, 2020 at 12:11

answered Jun 30, 2020 at 19:41

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mark C Over a year ago

I want to include NULLs as well, if you know it off the top of your head. Otherwise, I'll implement it myself.

John Cappelletti Over a year ago

@MarkC NULLS are excluded from the JSON version. However, in the XML version there is a tweak that will pass a NULL as an empty string (not a null)

Mark C Over a year ago

I am using the XML version. That tweak would work just fine.

John Cappelletti Over a year ago

@MarkC Forgot the Where Item not in ('RowID') --- updated

Mark C Over a year ago

I am not on 2016+. Thank you for the extensive help.

|

Gordon Linoff · Accepted Answer · 2020-06-30 18:59:30Z

1

As a general approach, union all works:

select seniority, 'seniority' as which, seniority - 1, 0
from t
union all
select p1, 'p1' as which, seniority - 1, 1
from t
union all
select r1, 'r1' as which, seniority - 1, 2
from t;

answered Jun 30, 2020 at 18:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Mark C Over a year ago

I wanted to see if someone had a good dynamic solution with UNPIVOT. I need to transform pretty much any table given into that format. I'm not sure if this is a great way as I'd have to generate this all with dynamic sql.

Collectives™ on Stack Overflow

SQL Converting Columns to Rows

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related