sql - SQL Server 中的动态多轴

Question

我是 SQL Server Pivot 的新手。我有一个输入表，其中的数据表示如下。它具有包含三个数量列的模型数据。根据提供的日期，这些金额适用于这些模型。

我正在尝试以动态方式生成如下所示的报告，其中每个模型的报告中应显示不同的日期数量和三个金额。

我尝试了如下所示的动态 SQL。

SELECT @pivotcols = STUFF((
                    SELECT ',' + QUOTENAME(DATE)
                    FROM #table
                    GROUP BY Date
                    ORDER BY Date
                    FOR XML PATH('')
                        ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT Model,' + @pivotcols + N' from 
             (
                    SELECT Model
        ,Amount1
        ,Amount2
        ,Amount3
        ,[DATE] AS DATE1
        ,[DATE] AS DATE2
        ,[DATE] AS DATE3
    FROM #table

            ) x
            pivot 
            (
                max(amount1)
                for Date1 in (' + @pivotcols + N')
            ) r
             pivot 
            (
                max(amount2)
                for Date2 in (' + @pivotcols + N')
            ) p
             pivot 
            (
                max(amount3)
                for Date3 in (' + @pivotcols + N')
            ) o '

EXEC sp_executesql @query;

当我尝试使用此查询时，我收到以下错误。

PIVOT 运算符中指定的列名“2000-01-01”与 PIVOT 参数中的现有列名冲突。

请对此有所了解。

标签： sqlsql-serversql-server-2012

Answer 1

最后我使用以下查询获得了结果

DECLARE @selectcols AS NVARCHAR(MAX) = ''
            ,@pivotcols AS NVARCHAR(MAX) = ''
            ,@query AS NVARCHAR(MAX) = ''

SELECT @selectcols = STUFF((
                    SELECT ',[AMT1-' + convert(VARCHAR(10), [DATE], 101) + ']'
                    + ',[AMT2-' + convert(VARCHAR(10), [DATE], 101) + ']'
                    + ',[AMT3-' + convert(VARCHAR(10), [DATE], 101) + ']'
                    + ',[TOT-' + convert(VARCHAR(10), [DATE], 101) + ']'
                    FROM #table
                    GROUP BY [DATE]
                    ORDER BY [DATE]
                    FOR XML PATH('')
                        ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @pivotcols = STUFF((
                    SELECT ',[AMT1-' + convert(VARCHAR(10), [DATE], 101) + ']'
                    FROM #table
                    GROUP BY [DATE]
                    ORDER BY [DATE]
                    FOR XML PATH('')
                        ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')


SET @query = N'SELECT [Model],' + @selectcols + N' from 
             (
                select [Model],AMT1,AMT2,AMT3,AMT1+AMT2+AMT3 AS TOT
                ,''AMT1-'' + convert(VARCHAR(10), [DATE], 101) as DATE1
                ,''AMT2-'' + convert(VARCHAR(10), [DATE], 101) as DATE2
                ,''AMT3-'' + convert(VARCHAR(10), [DATE], 101) as DATE3
                ,''TOT-'' + convert(VARCHAR(10), [DATE], 101) as DATE4
                from #table
            ) x
            pivot 
            (
                max(AMT1)
                for DATE1 in (' + @pivotcols + N')
            ) r
             pivot 
            (
                max(AMT2)
                for DATE2 in (' + replace(@pivotcols,'AMT1','AMT2') + N')
            ) p
             pivot 
            (
                max(AMT3)
                for DATE3 in (' + replace(@pivotcols,'AMT1','AMT3') + N')
            ) o
            pivot 
            (
                max(TOT)
                for DATE4 in (' + replace(@pivotcols,'AMT1','TOT') + N')
            ) t'

EXEC sp_executesql @query;