按月份列出的动态透视表

ef1yzkbh 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(389)

我尝试在sql中创建一个动态透视表，它将基于月份和年份进行报告。我做了一系列研究，得出了以下问题：

declare @dynamic nvarchar(max),
@column nvarchar(max);

set @column = N'';
select @column += N'' + datename(month,incurdate) +' '+ datename(year,incurdate) + ',' 
from (select distinct a.incurdate from artable a) as Transpose

select @column = substring(@column,0,len(@column))

set @dynamic = 'select * from
 (
select month, incurdate, dolamount
from artable join dolentry on month = period
) b
pivot(sum(dolamount) for incurdate in (' + @column + ')) as PivotTable'

execute sp_executesql @dynamic

我能够成功地打印@column变量，但是当我尝试在@dynamic变量中设置它时，问题就出现了。错误消息是'1990'附近的'msg 102，level 15，state 1，line 6语法错误。'1990是第一个数据透视列的第一年。任何帮助或提示都将不胜感激。谢谢！

sql pivot tsql dynamic quotename

来源：https://stackoverflow.com/questions/63238895/dynamic-pivot-table-by-month

1条答案

按热度按时间

tkqqtvp11#

您需要在以下代码中使用quotename：

select @column += N'' + QUOTENAME(datename(month,incurdate) +' '+ datename(year,incurdate)) + ',' 
from (select distinct a.incurdate from artable a) as Transpose

为了得到这样的输出：

[col01], [col02], [col03], ... , [col04]

从文件中可以看出 PIVOT 语法要求旋转列用方括号括起来：

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

赞(0）回复(0）举报 2021-07-24

我来回答

按月份列出的动态透视表

1条答案

相关问题

热门标签

最新问答