sql server 中的动态 sql pivot [英] dynamic sql pivot in sql server
问题描述
现在在 sql server 2008 中运行此查询,但很快计划将其移动到在 sql 报告服务中创建报告:
Running this query in sql server 2008 now, but soon plan to move it in creating a report in sql reporting services:
SELECT * from ( SELECT Amount, Year, column1, column2,column3 from BUYSCTE ) BUY
我的表结果在没有数据透视的查询之上运行是这样的
My table results running above query without the pivot is this
Column1 | Column2 | Column3| FYYear| Amount|
1 cat dog 2011 50
1 cat dog 2012 75
1 cat dog 2013 65
2 fish snake 2011 23
2 fish snake 2012 39
2 fish snake 2013 59
..
..
.. 2016
所以基本上我希望结果是这样的:
so basically I want the results to end up like this:
Column1 | Column2 | Column3| 2011| 2012 | 2013
1 cat dog 50 75 65
2 fish snake 23 39 59
所以为了实现这一点,我想出的查询是我首先创建了一个表变量并将其设为动态变量,然后像这样进行了透视,
so the query I came up with to make this happen is I first created a table variable and made it a dynamic variable and did a pivot like this,
declare @Year nvarchar(Max)
set @Year = STUFF(
(SELECT ', ' + quotename(FYYear)
from BUYSCTE Group By FYYear order by
FYYear For XML PATH(''))
, 1, 2, '');
上面用 [2011]、[2012]、[2013]、[2014]、[2015]、[2016] 填充 @Year 值
The above populates the @Year value with: [2011], [2012], [2013], [2014], [2015], [2016]
我知道因为我将上面的选择更改为 SELECT @Year 只是为了检查值是否设置正确
I know that because I change the above select to SELECT @Year just to check if the value is set correctly
从那里我将表变量之后的上述选择更改为
from there I change the above select after the table variable to
SELECT * from ( SELECT Amount, FYYear, column1, column2,column3 from BUYSCTE ) BUY
PIVOT( SUM(Amount) FOR FYYear in ([@Year]) ) pvt
但我的结果最终是这样的
but my results end up being this
Column1 | Column2 | Column3| @Year|
1 cat dog null
2 fish snake null
我做错了什么?似乎我错过了一些我看不到自己的小东西.即使我将聚合函数从 SUM 更改为 COUNT,它也会为 @Year 列值提供 0 而不是 null
What am I doing wrong? it seems like something small I am missing that I cannot see myself. even if I change the aggregate function from SUM to COUNT it gives 0 for the @Year column values instead of null
推荐答案
你已经很接近了,但要让它工作,你必须使用动态 SQL 构造你的 PIVOT
然后执行它.因此,在填充变量 @Year 之后,您需要执行以下操作:
You are close, but for this to work you have to construct your PIVOT
using dynamic SQL and then execute it. So, after you populate your variable @Year, you need to do something like this:
DECLARE @Query VARCHAR(MAX)
SET @Query = '
SELECT * from ( SELECT Amount, FYYear, column1, column2,column3 from BUYSCTE ) BUY
PIVOT( SUM(Amount) FOR FYYear in ('+ @Year + ') ) pvt'
EXEC(@Query)
虽然在这样做之前,您应该先看看这个链接.
Though before doing this, you should take a look at this link.
这篇关于sql server 中的动态 sql pivot的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!