挣扎与SQL枢轴查询语法

Question

谁能告诉我我的SQL出了什么问题?错误是:

156号电话，第15层，第1州，第11行

关键字'select'附近语法错误。

留言102,15层，状态1,11行

')'附近语法错误。

SELECT *
FROM (
    SELECT 
        left(datename(month,TransactionDateTime),3) as [month], year(TransactionDateTime) as [year],
        count(*) as Total 
    FROM quotations
) as s
PIVOT
(
    SUM(Total)
    FOR [year] IN (select distinct year(TransactionDateTime) from quotations)
) AS pivot

我追求的形状是……以年作为列名，然后每个月12行。下面只是为了说明形状

        //   var data = google.visualization.arrayToDataTable([
        //  ['Month', '2013', '2014', '2015'],
        //  ['Jan', 10, 30, 31],
        //  ['Feb', 11, 30, 32],
        //]);

Answer 1

pivot的语法如下(来自TechNet):

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>

您可以看到，在IN子句中应该有包含在[]中的列名，如果列名是动态的，这可能是一个问题。这个问题可以通过使用动态SQL轻松解决。

下面是一个例子:

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(TransactionDateTime))
            FROM Quotations
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
SET @query = 
    'SELECT *
    FROM (
        SELECT 
            left(datename(month,TransactionDateTime),3) as [month], year(TransactionDateTime) as [year],
            count(*) as Total 
        FROM quotations
    ) as s
    PIVOT
    (
        SUM(Total)
        FOR [year] IN (' + @cols + ')
    ) AS pivot'
EXECUTE(@query)

我不知道你的模式是什么，所以表/列名可能有错误。如果这没有帮助，我可以更精确，如果你分享你的模式和一个样本数据。

Answer 2

问题在于您的查询的以下部分

(select distinct year(TransactionDateTime) from quotations)

您需要在in子句中提供一个静态年份列表。