具有动态字段的SQL Server Pivot [英] SQL Server Pivot with Dynamic Fields
本文介绍了具有动态字段的SQL Server Pivot的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
试图找出在下表的日期字段上动态透视表的最佳方法.问题是这些日期会更改,因此我无法在数据透视表中对其进行硬编码.
Trying to figure out the best way to pivot a table dynamically on the date field in the following table. the issue is these dates change so I cant hard code them in the pivot statement.
id value date
1 55 2012-12-29 00:00:00:00
1 54 2012-10-29 00:00:00:00
1 89 2013-02-02 00:00:00:00
2 45 2012-12-29 00:00:00:00
2 54 2012-10-29 00:00:00:00
4 78 2012-12-29 00:00:00:00
4 90 2012-10-29 00:00:00:00
4 23 2012-10-29 00:00:00:00
我希望输出看起来像这样
I want the output to look like this
id 2012-12-29 00:00:00:00 2012-10-29 00:00:00:00 2013-02-02 00:00:00:00
1 55 54 89
2 45 54 null
4 78 90 23
推荐答案
由于使用的是SQL Server,因此可以使用PIVOT
函数.
Since you are using SQL Server, then you can use the PIVOT
function.
如果您的值已知,则可以对值进行硬编码:
If your values are known, then you can hard-code the values:
select *
from
(
select id, value, date
from yourtable
) src
pivot
(
max(value)
for date in ([2012-12-29], [2012-10-29], [2013-02-02])
) piv
请参见带演示的SQL提琴.
但是,如果它们未知,那么您将需要使用动态sql:
But if they are unknown, then you will need to use dynamic sql:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(50), date, 120))
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + ' from
(
select id, value, convert(varchar(50), date, 120) date
from yourtable
) x
pivot
(
max(value)
for date in (' + @cols + ')
) p '
execute(@query)
请参见带演示的SQL提琴.
这两个查询的结果是:
| ID | 2012-10-29 00:00:00 | 2012-12-29 00:00:00 | 2013-02-02 00:00:00 |
------------------------------------------------------------------------
| 1 | 54 | 55 | 89 |
| 2 | 54 | 45 | (null) |
| 4 | 90 | 78 | (null) |
这篇关于具有动态字段的SQL Server Pivot的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文