sql - 以水平格式显示 SQL 结果

Question

我想要的只是改变这个:

Period  | Department    | Print | Copy
---------------------------------------
201601  | Dept 1        | 10    | 20
201601  | Dept 2        | 20    | 10
201602  | Dept 1        | 30    | 40
201602  | Dept 2        | 40    | 30
201603  | Dept 1        | 50    | 60
201603  | Dept 2        | 60    | 50

进入此:

Department  | 201601 Print  | 201601 Copy   | 201602 Print  | 201602 Copy   | 201603 Print  | 201603 Copy
------------------------------------------------------------------------------------------
Dept 1      | 10            | 20            | 30            | 40            | 50            | 60
Dept 2      | 20            | 10            | 40            | 30            | 60            | 50

我尝试使用 PIVOT 构建脚本，但我不知道如何在列中显示每个周期的“打印”和“复制”。此外，由于“Period”的值是未知的，因此我也无法在脚本中硬编码该值。

这是我的尝试:

SELECT [Department]
    ,[201601] AS [201601 Copy]
    ,[201602] AS [201602 Copy]
    ,[201603] AS [201603 Copy]
FROM
    (SELECT [Copy], [Period], [Department] from #tempTable) AS ST
PIVOT
    (SUM([Copy]) FOR [Period] IN ([201601],[201602],[201603])) AS PT

这是使用我的示例数据创建表的脚本:

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
    DROP TABLE #tempTable
CREATE TABLE #tempTable(
    [Period] varchar(50)
    ,[Department] varchar(50)   
    ,[Print] int
    ,[Copy] int
)
INSERT INTO #tempTable VALUES 
     ('201601', 'Dept 1', 10, 20)
    ,('201601', 'Dept 2', 20, 10)
    ,('201602', 'Dept 1', 30, 40)
    ,('201602', 'Dept 2', 40, 30)
    ,('201603', 'Dept 1', 50, 60)
    ,('201603', 'Dept 2', 60, 50)

感谢您的提前回复。

回答

我研究了收到的答案，最终构建了以下脚本:

DECLARE @sql AS varchar(max);
SELECT @sql = 'SELECT [Department],' + 
    STUFF((
        SELECT DISTINCT
            ',SUM(ISNULL(CASE [Period] WHEN ''' + [Period] + ''' THEN [Print] END, 0)) AS [' + [period] + ' Print]' +
            ',SUM(ISNULL(CASE [Period] WHEN ''' + [Period] + ''' THEN [Copy]  END, 0)) AS [' + [period] + ' Copy]'
        FROM #TempTable
        FOR XML PATH('')
    ), 1, 1, '') +
    'FROM #TempTable 
    GROUP BY [Department]';
PRINT @sql
EXEC(@sql);

Answer 1

您可以使用动态sql查询。

查询

declare @sql as varchar(max);
select @sql = 'select [Department],' + stuff((
    select distinct ',max(case [Period] when ' + char(39) + [Period] + char(39) + 
    ' then [Print] end) [' + [period] + ' Print]'
    + ',max(case [Period] when ' + char(39) + [Period] + char(39) + 
    ' then [Copy] end) [' + [period] + ' Copy]'
    from #TempTable
    for xml path('')
), 1, 1, '');

select @sql += ' from #TempTable group by [Department];';
exec(@sql);