时间戳

首页 > 解决方案 > 将数据行转换为列并按月显示

sql - 将数据行转换为列并按月显示

问题描述

我有一个下表： -

CREATE TABLE #TmpData (
 RType Varchar(10),
 Client VARCHAR(10),
 TRequest INT,
 Due INT,
 Month INT
  );

  INSERT INTO #TmpData VALUES ('P', 'Name1',54,67,1),('P', 'Name5',5,47,1),('A', 'Name6',3,90,1 ),('A', 'Name3',4,43,1 ),('P', 'Name1',0,10,2)
  ,('P', 'Name2',1,78,2),('P', 'Name4',54,67,3),('P', 'Name3',5,47,3),('A', 'Name6',3,90,3 ),('P', 'Name8',3,233,3 ),('P', 'Name1',23,457,4)
  ,('P', 'Name7',3,6,4 ),('P', 'Name8',65,548,4 ),('P', 'Name2',3,56,5 ),('P', 'Name3',65,87,6 ),('P', 'Name4',3,56,7 ),('P', 'Name9',65,87,7 )

 SELECT * FROM #TmpData

我想将其数据行明智地转换为列并按月显示。我附上了我需要的结果集的屏幕截图。

结果集

例如，在这里我花了 7 个月，但它可以长达 12 个月。

请帮忙。

谢谢

标签： sqlsql-server

解决方案

您可以尝试使用CASE WHENwithSUM函数。

CASE WHEN比较monththen SUMthatTRequest或Due group byby
RType,Client

SELECT RType,
       Client,
       SUM(CASE WHEN Month = 1 THEN TRequest ELSE 0 END) 'Jan Totle',
       SUM(CASE WHEN Month = 1 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 2 THEN TRequest ELSE 0 END) 'Feb Totle',
       SUM(CASE WHEN Month = 2 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 3 THEN TRequest ELSE 0 END) 'March Totle',
       SUM(CASE WHEN Month = 3 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 4 THEN TRequest ELSE 0 END) 'April Totle',
       SUM(CASE WHEN Month = 4 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 5 THEN TRequest ELSE 0 END) 'May Totle',
       SUM(CASE WHEN Month = 5 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 6 THEN TRequest ELSE 0 END) 'June Totle',
       SUM(CASE WHEN Month = 6 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 7 THEN TRequest ELSE 0 END) 'July Totle',
       SUM(CASE WHEN Month = 7 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 8 THEN TRequest ELSE 0 END) 'Aug Totle',
       SUM(CASE WHEN Month = 8 THEN Due ELSE 0 END) '#of Request',
       SUM(CASE WHEN Month = 9 THEN TRequest ELSE 0 END) 'Sep Totle',
       SUM(CASE WHEN Month = 9 THEN Due ELSE 0 END) '#of Request',
       ....
FROM TmpData
GROUP BY RType,
       Client

sqlfiddle

编辑

如果要使month列动态化，可以使用动态枢轴。

DECLARE @cols AS NVARCHAR(MAX),
        @Pivotcols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);



SET @Pivotcols = STUFF((SELECT DISTINCT 
                                ',SUM(CASE WHEN Month = ' + CAST(Month AS VARCHAR(5)) + ' THEN TRequest ELSE 0 END) as ''' + FORMAT(DATEADD(mm,Month,'1900-01-01'),'MMM') + ' Totle ''',
                                ',SUM(CASE WHEN Month = ' + CAST(Month AS VARCHAR(5)) + ' THEN Due ELSE 0 END) as ''' + FORMAT(DATEADD(mm,Month,'1900-01-01'),'MMM') + ' Totle '''
            FROM TmpData 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


set @query = 'SELECT RType,Client, '+  @Pivotcols + ' FROM TmpData GROUP BY RType,Client'



execute(@query)

SQLFIDDLE 动态数据透视

推荐阅读