我目前正在将MS Access db移动到SQL Server。我有一个cross-tab query我需要转换为Pivot table。
Pivot table将位于一个sproc中，该给出了month和year的参数。这些参数被放入subquery的WHERE子句中。
到目前为止的 SQL：
SELECT IDNbr, [Name]
FROM (SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance
FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year) as d1
PIVOT (
SUM(Balance)
For Balance in ([BalDate]) --Error: see below
) piv;
我在Pivot部分中遇到的错误是：
在 PIVOT 运算符中指定的列名"BalDate"与 PIVOT 参数中的现有列名冲突。
而且我也收到错误：
">BalDate"列被多次指定为"piv"。
当前数据：
Customer | BalDate | Balance
----------+-------------+--------
Customer1 | 1/01/2017 | 0.00
Customer1 | 1/02/2017 | 0.00
Customer1 | 1/03/2017 | 0.00
Customer1 | 1/04/2017 | 0.00
.... | .... | ....
所需数据：
| Customer | 01/01/2017 | 01/02/2017 | 01/03/2017 | 01/04/2017 | 01/05/2017 | 01/06/2017 | 01/07/2017 | 01/08/2017 | 01/09/2017 | 01/10/2017 | ....
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Customer1 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ....
| Customer2 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ....
| Customer3 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ....
主要问题：我将如何纠正Pivot Table中的错误？
次要问题：我怎样才能让Current dataDesired data？
我仍然是SQL和SQL Server的初学者，所以如果答案很明显，我深表歉意。
提前感谢您的任何帮助和建议！我很乐意尝试澄清任何看起来令人困惑或不清楚的事情！
我引用并尝试适用于我的情况的帖子：
SQL 透视多列
透视运算符中指定的列名冲突
多次指定列
如何创建交叉表查询
SQL Server Pivot 列数据
您可以尝试使用动态 SQL 进行BalDate。
由于您的查询具有参数，因此您可以尝试在执行语法中使用sp_executesql和附加参数。
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(a1.BalDate)
FROM IDTable a1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT *
FROM (
SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance
FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year
) as d1
PIVOT (
SUM(Balance)
For Balance in ('+ @cols +') --Error: see below
) piv'
EXECUTE sp_executesql @query, N'@year INT,@month INT',
@year = @year,
@month = @month
听起来你需要一个动态枢轴，
首先，您必须选择要在枢轴中使用的所有日期， 日期必须采用引号格式，即 [日期]，否则透视将无法理解。
DECLARE @Days NVarchar(MAX)
SEt @Days = STUFF((SELECT ',' + QUOTENAME(a1.BalDate)
FROM IDTable a1
group by a1.BalDate
order by a1.BalDate
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
Select @Days
然后你必须建立一个动态枢轴，像这样。
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery = 'SELECT *
INTO #ToReportOn
FROM
(SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance
FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year) as d1
) A
PIVOT
(
SUM(Balance)
For BalDate in ('+@Days +')
) B
ORDER BY document_group
SELECT * FROM #ToReportOn
'
--EXECUTE(@FinalQuery)
PRINT @FinalQuery
我没有测试这个。但我认为动态枢轴是解决您问题的方法。
在MS Sql Server中，您需要一个动态sql。
首先计算一个带有日期的变量。
然后在动态查询的字符串中使用它。
您可以在rextester上测试它
DECLARE @Dates NVARCHAR(max);
SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalDate))
FROM IDTable
WHERE YEAR(BalDate) = @year
AND MONTH(BalDate) = @month
GROUP BY BalDate
ORDER BY BalDate;
DECLARE @DynSql NVARCHAR(max);
SET @DynSql = 'SELECT *
FROM
(
SELECT a1.IDNbr, a2.[CustName] as [Name], a1.BalDate, a1.Balance
FROM IDTable a1
INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
WHERE MONTH(a1.BalDate) = @month
AND YEAR(a1.BalDate) = @year
) as src
PIVOT (
SUM(Balance)
FOR BalDate IN ('+ @Dates +')
) pvt';
DECLARE @Params NVARCHAR(500) = N'@year INT, @month INT';
-- SELECT @DynSql AS DynSql;
EXECUTE sp_executesql @DynSql, @Params, @year = @year, @month = @month;