Dynamic SQL to Unpivot Data using Cross Apply with Multiple Columns

Question

Microsoft SQL Server Management Studio v18.8

I have a table that will have varying columns and column names. I need to unpivot the data so I can then eventually store it into a different table. Unfortunately, this is the process since the original table is being pulled from a Google Sheet.

I looked up several articles and answers on here, but I was unable to replicate any of them successfully. I need to unpivot based on the Item, Timestamp, and Location. Then Q1, Q2, Q3, etc. should be unpivoted. Below is an example table and query that will get my desired results. Any assistance in getting this in dynamic SQL for future column additions/modifications would be greatly appreciated. I am open to using UNPIVOT or any other function as well to get the desired results. The actual data source will be a permanent table, not a temp table.

Create Table

DROP TABLE IF EXISTS #test
CREATE TABLE #test (Item VARCHAR(16), Timestamp DATETIME, Location VARCHAR(2), Q1 VARCHAR(3), Q2 VARCHAR(3), Q3 VARCHAR(3))
INSERT INTO #test VALUES('Stapler','2021-04-14 12:00:00.000', 'US','Yes','No','Yes'),
                        ('Paper','2021-04-10 16:00:00.000', 'CA','No','Yes','Yes'),
                        ('Pen','2021-04-06 15:00:00.000','MX','Yes','Yes','No')

Unpivot using Cross Apply

 SELECT A.Item,
           A.Timestamp,
           A.Location,
           B.*
      FROM #test AS A
    CROSS APPLY
    (
     VALUES ('Q1', A.Q1),
            ('Q2', A.Q2),
            ('Q3', A.Q3)
    ) B (Question,Answer)

John Cappelletti · Accepted Answer · 2021-04-28T19:09:10.940

5

You can use a bit of JSON to dynamically unpivot your data. If not 2016+ ... there is a similar XML approach.

Example

Select A.Item
      ,A.Timestamp
      ,A.Location
      ,B.*
 From  #test A
 Cross Apply (
                Select Question = [Key]
                      ,Answer   = [Value]
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                 Where [Key] not in ('Item','Timestamp','Location')
             ) B

Results

EDIT - Update for XML Version

Select A.Item
      ,A.Timestamp
      ,A.Location
      ,C.*
 From  #test A
 Cross Apply ( values ((Select A.* for XML RAW,Type)) )B(XMLData)
 Cross Apply (
                Select Question = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Answer   = xAttr.value('.','varchar(max)')
                 From XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)', 'varchar(100)')  not in ('Item','Timestamp','Location')
             ) C

edited Apr 28 '21 at 19:09

answered Apr 28 '21 at 18:09

John Cappelletti

79,615
7
44
66

This is excellent! The JSON method worked perfectly. I'll have to research more into how JSON and the functions you used within it work. I didn't see this method in anything so far. – MonkeyMonkey Apr 28 '21 at 18:25
@MonkeyMonkey Always happy to help, Dipping into the JSON pool is well worth your time. – John Cappelletti Apr 28 '21 at 18:28
3

Pretty neat. Add `,TYPE` to the XML version, if you do `(Select A.* for XML RAW, TYPE)` then you don't need to convert. Also `text()` is faster than `.` – Charlieface Apr 28 '21 at 18:53

Dynamic SQL to Unpivot Data using Cross Apply with Multiple Columns

1 Answers1

Related