以水平格式显示SQL结果

我只想改变这个：

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',30,40),40,30),('201603',50,60),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,'') +
    'FROM #TempTable 
    GROUP BY [Department]';
PRINT @sql
EXEC(@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('')
),'');

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

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!