Search code examples
sqlsql-serverunpivotcross-apply

Dynamic SQL to Unpivot Data using Cross Apply with Multiple Columns

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

  • 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

    enter image description here

    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