CODEWITHSUNDEEP
sqlsql-server
Rahul Aggarwal
Rahul Aggarwal

Reputation: 45

Convert data row to column and show it month wise

I have a following table:-

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

I want to Convert its data row wise to column and show it monthwise. I am attaching the screenshot of my resultset which I need.

Result Set

Here for example I have taken 7 months but it can go upto 12 months.

Please Help.

Thanks

Upvotes: 3

Views: 105

Answers (1)

D-Shih
D-Shih

Reputation: 46229

You can try to use CASE WHEN with SUM function.

CASE WHEN compare the month then SUM that TRequest or Due group by by
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

EDIT

if you want to make month column dynamic, you can use dynamic pivot.

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 dynamic pivot

Upvotes: 2

Related Questions