SQL Server 2012动态数据透视表-交叉表的串联 [英] SQL Server 2012 dynamic pivot - concatenation for cross tab
问题描述
我可以在动态枢轴中使用串联作为交叉表变量吗？
Can I use a concatenation as the cross tab variable in a dynamic pivot?
我正在尝试生成交易价值的交叉表表，其中列标题是基于[交易日期]日期字段的[yyyy-mm]日期顺序。
I'm trying to generate a cross tab table of values of transactions, where the column headers are [yyyy-mm] in date order based on a [transaction date] date field.
这是数据：-
RW_Ref ,Transaction_Date ,Transaction_Type ,Transaction_Value ,Batch_Number ,Month
1 , 03/09/2009 ,11 ,30 ,999
1 , 05/10/2009 ,11 ,25 ,999
1 , 11/10/2009 ,11 ,15 ,999
1 , 15/11/2009 ,11 ,40 ,999
1 , 03/12/2009 ,11 ,30 ,999
2 , 05/10/2009 ,11 ,60 ,999
2 , 11/11/2009 ,11 ,70 ,999
2 , 01/12/2009 ,11 ,60 ,999
2 , 03/12/2009 ,11 ,15 ,999
3 , 05/11/2009 ,11 ,25 ,999
3 , 06/11/2009 ,11 ,35 ,999
3 , 07/11/2009 ,11 ,60 ,999
这就是我要找的东西：-
And this is what I'm trying to get back:-
Transaction_Type,Batch_Number,[2009-09],[2009-10],[2009-11],[2009-12]
11 ,999 ,30 ,40 ,40 ,30
11 ,999 , ,60 ,70 ,75
11 ,999 , , ,120 ,
这是我得到的代码到目前为止：-
This is the code I've got so far:-
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ', '
+ QUOTENAME(CONCAT(datepart(yyyy,[Transaction_Date]) , '-', RIGHT('00' + CONVERT(NVARCHAR(2), datepart(M,[Transaction_Date])), 2)))
from [TX].[dbo].[Transactions] ORDER by 1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query =
'SELECT [RW_Ref], [Transaction_Type], [Transaction_Value], [Batch_Number], '
+ @cols
+ ' '
+'FROM [TX].[dbo].[Transactions]
PIVOT (
SUM([Transaction_Value])
FOR
CONCAT(
datepart(yyyy,[Transaction_Date]), '
+ QUOTENAME('-','''')
+ ', RIGHT(' + QUOTENAME('00','''') + ' + CONVERT(NVARCHAR(2), datepart(M,[Transaction_Date])), 2))
) p '
print @query
execute(@query)
Print @Query产生以下内容：-
The Print @Query produces this:-
SELECT [RW_Ref], [Transaction_Type], [Transaction_Value], [Batch_Number], [2009-09], [2009-10], [2009-11], [2009-12], [2016-07] FROM [TX].[dbo].[Transactions]
PIVOT (
SUM([Transaction_Value])
FOR
CONCAT(
datepart(yyyy,[Transaction_Date]), '-',
RIGHT('00' + CONVERT(NVARCHAR(2), datepart(M,[Transaction_Date])), 2))
) p
但是这会生成一条消息
消息102，级别15，状态1，第5行
'（'附近的语法不正确。
第5行是CONCAT（我想。我可以使用Concat函数以这种方式生成列标题吗？还是我只需要使用一条记录（这意味着我需要将该字段添加为列，我宁愿不这样做，也不是我的桌子）
but this generates a message Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '('. Line 5 is the CONCAT( I think. Can I use the Concat fun ction to generate the column headings in this way or do I have to use a record only (which means I need to add that field as a column which I'd rather not do, it's not my table)
谢谢！
推荐答案
您是否尝试过以下代码？
编辑：在select和in语句中都需要原始日期值。您必须修改查询的 select @cols = ...部分以返回原始值值（除去列中的datepart内容）作为列名，并根据需要对其进行别名。IN运算符要求完整值。
Have you tried the following code? you need original date values both in select and in statements. You have to modify your "select @cols = ..." part of the query to return original values (get rid of the datepart stuff in the column) as column names and alias them as you wish. The IN operator requires full values.
set @query =
'SELECT [RW_Ref], [Transaction_Type], [Batch_Number], '
+ @cols
+ ' '
+'FROM [TX].[dbo].[Transactions]
PIVOT (
SUM([Transaction_Value])
FOR
[Transaction_Date] IN ( '
+ @cols + ')
) p '
请告诉我是否可行。请参见下面的示例，您可以在TSQL2012示例数据库上运行该示例，它应该可以帮助您了解透视语句的工作原理。
Please let me know if it works. See an example below, which you can run on TSQL2012 sample database, it should help you understand how the pivot statement works.
select shipperid, [2006-07-29 00:00:00.000] as '2006/07', [2006-08-12 00:00:00.000] as '2006/08'
from sales.orders
pivot (sum (freight) for shippeddate in ([2006-07-29 00:00:00.000], [2006-08-12 00:00:00.000])) as p
这篇关于SQL Server 2012动态数据透视表-交叉表的串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
