1

How do I do a sql pivot of data that looks like this, USING the SQL PIVOT command ?

id           |    field     |   value
---------------------------------------
1            |    year      |   2011
1            |    month     |   August
2            |    year      |   2009
1            |    day       |   21
2            |    day       |   31
2            |    month     |   July
3            |    year      |   2010
3            |    month     |   January
3            |    day       |   NULL

Into something that looks like this:

id  |  year  |  month  |  day
-----------------------------
1     2011     August    21
2     2010      July     31
3     2009     January   NULL
Ben
  • 51,770
  • 36
  • 127
  • 149
djangofan
  • 28,471
  • 61
  • 196
  • 289
  • 1
    It's probably be better to change the table instead of doing fancy queries to make it look like it ought to. – Ben Dec 05 '11 at 22:58
  • @Ben - Agreed, there are _far_ better ways to store this data, even considering some 'fields' might need to be `null`. If those month names are actually string, shoot the designer. Mutilate whoever decided to store `null` keyed values for `day` attributes (instead of leaving off the attribute altogether). I'm not sure why this was setup this way, but I believe a calendar table + date-portion tables (and a couple views) would take less space, provide FK/datatype safety, and _probably_ be more performant. – Clockwork-Muse Dec 05 '11 at 23:31
  • This is a common scenerio caused by a developer creating a metadata table that wants to be flexible in the number of metadata fields are associated with each ID. – djangofan Dec 06 '11 at 01:08

3 Answers3

2
;WITH DATA(id,field,value) AS
(
SELECT 1,'year','2011' UNION ALL
SELECT 1,'month','August' UNION ALL
SELECT 2,'year','2009' UNION ALL
SELECT 1,'day ','21' UNION ALL
SELECT 2,'day ','31' UNION ALL
SELECT 2,'month','July' UNION ALL
SELECT 3,'year','2010' UNION ALL
SELECT 3,'month','January' UNION ALL
SELECT 3,'day ',NULL
)
SELECT id,
       year,
       month,
       day
FROM   DATA PIVOT (MAX(value) FOR field IN ([year], [month], [day])) AS Pvt
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • This answer also worked. Both of your answers were so similar... I gave the points to rsbarro. Thanks for your help though. – djangofan Dec 06 '11 at 01:27
2

Try something like this:

DECLARE @myTable AS TABLE([ID] INT, [Field] VARCHAR(20), [Value] VARCHAR(20))
INSERT INTO @myTable VALUES ('1', 'year', '2011')
INSERT INTO @myTable VALUES ('1', 'month', 'August')
INSERT INTO @myTable VALUES ('2', 'year', '2009')
INSERT INTO @myTable VALUES ('1', 'day', '21')
INSERT INTO @myTable VALUES ('2', 'day', '31')
INSERT INTO @myTable VALUES ('2', 'month', 'July')
INSERT INTO @myTable VALUES ('3', 'year', '2010')
INSERT INTO @myTable VALUES ('3', 'month', 'January')
INSERT INTO @myTable VALUES ('3', 'day', NULL)

SELECT [ID], [year], [month], [day]
FROM
(
      SELECT [ID], [Field], [Value] FROM @myTable
) t
PIVOT
(
    MIN([Value]) FOR [Field] IN ([year], [month], [day]) 
) AS pvt
ORDER BY pvt.[year] DESC

Which will yield results of:

ID  year    month   day
1   2011    August  21
3   2010    January NULL
2   2009    July    31
rsbarro
  • 27,021
  • 9
  • 71
  • 75
0
SELECT
id,
MAX(CASE WHEN RK=3 THEN VAL ELSE '' END) AS "YEAR",
MAX(CASE WHEN RK=2 THEN VAL ELSE '' END) AS "MONTH",
MAX(CASE WHEN RK=1 THEN VAL ELSE '' END) AS "DAY"

FROM 
(
SELect
ID,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY YEAR1 ASC) RK,
VAL
FROM TEST3)A
GROUP BY 1
ORDER BY 1;
sjaladurgam
  • 1