Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- id | year | month | day
- -----------------------------
- 1 2011 August 21
- 2 2010 July 31
- 3 2009 January NULL
- 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
- ID year month day
- 1 2011 August 21
- 3 2010 January NULL
- 2 2009 July 31
- ;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
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement