三列SQL PIVOT [英] Three column SQL PIVOT

问题描述

如何使用SQL PIVOT命令对看起来像这样的数据进行sql透视?

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

变成这样的东西:

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

这篇关于三列SQL PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！