我如何使用SQL PIVOT命令对看起来像这样的数据进行sql透视？
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
