Pivot/dynamic SQL help required

Question

I need to convert the following data (simplified for SO purposes):-

DataTitle   DataValue   Id
==============================
Thing 1     Data 1      12345
Thing 2     Data 2      12345
Thing 3     Data 3      12345
Thing 4     Data 4      12345
Thing 1     Data 5      23456
Thing 2     Data 6      23456
Thing 3     Data 7      23456
Thing 4     Data 8      23456

Into this:-

ID      Thing1    Thing2    Thing3    Thing4
==============================================
12345   Data1     Data2     Data3     Data4
23456   Data5     Data6     Data7     Data8

Basically need to group by the ID, and then for each 'Thing' I then need a column. For the benefits of this example there will always be the same number of 'Things' in the table, so the numbers of columns isn't subject to change. I can't change the starting table structure at this stage, so unfortunately the solutions can't be a rethink of the table design...

I've looked at using PIVOT to solve this, but as I'm not aggregating the data I got really stuck..

Is there some syntax to the PIVOT command that would help me achieve this, or do I need something more dynamic in nature.

Thanks in advance

What you are looking for is simply a dynamic pivot. You're overthinking the aggregation part. Think of it this way, if you apply MAX() when there is only 1 row, it will simply return that 1 row(alternatively you could use MIN because it's still just aggregating 1 row). Your pivot statement should look something like: *PIVOT (MAX(DataValue) FOR DataTitle IN([Thing 1],[Thing 2],[Thing 3],[Thing 4])) AS pvt* — Stephan, Jun 30 '15 at 16:04
Making it dynamic from there is simple enough. There are many examples here on SO and on the Web in general — Stephan, Jun 30 '15 at 16:05

score 1 · Answer 1 · answered Jun 30 '15 at 12:41

If you are only ever going to have the same 4 things, you could use a query such as this to produce your output:

CREATE TABLE #DemoTable (DataTitle VARCHAR(10), DataValue VARCHAR(10), Id INT)

INSERT INTO #DemoTable (DataTitle, DataValue, Id) VALUES ('Thing 1', 'Data 1', '12345'),('Thing 2', 'Data 2', '12345'),('Thing 3', 'Data 3', '12345'),('Thing 4', 'Data 4', '12345'),('Thing 1', 'Data 5', '23456'),('Thing 2', 'Data 6', '23456'),('Thing 3', 'Data 7', '23456'),('Thing 4', 'Data 8', '23456')

SELECT  DISTINCT
        dt.Id,
        dt1.DataValue AS Thing1,
        dt2.DataValue AS Thing2,
        dt3.DataValue AS Thing3,
        dt4.DataValue AS Thing4
  FROM  #DemoTable dt
    INNER JOIN #DemoTable dt1 ON dt1.Id = dt.Id AND dt1.DataTitle = 'Thing 1'
    INNER JOIN #DemoTable dt2 ON dt2.Id = dt.Id AND dt2.DataTitle = 'Thing 2'
    INNER JOIN #DemoTable dt3 ON dt3.Id = dt.Id AND dt3.DataTitle = 'Thing 3'
    INNER JOIN #DemoTable dt4 ON dt4.Id = dt.Id AND dt4.DataTitle = 'Thing 4'

DROP TABLE #DemoTable

This is effectively pivoting on your Id column and getting the DataValue for each individual thing in the four output columns.

Pivot/dynamic SQL help required

1 Answers1