locked
Convert data row wise to column and show it month wise RRS feed

  • Question

  • User1216489373 posted

    Hi Experts,

    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.

    RType Client Jan Total # of Requests Feb Total # of Requests March Total # of Requests April Total # of Requests May Total # of Requests June Total # of Requests July Total # of Requests
    P Name1 54 67 0 10 0 0 23 457 0 0 0 0 0 0
    P Name5 5 47 0 0 0 0 0 0 0 0 0 0 0 0
    A Name6 3 90 0 0 3 90 0 0 0 0 0 0 0 0
    A Name3 4 43 0 0 5 47 0 0 0 0 65 87 0 0
    P Name4 0 0 1 78 0 0 0 0 0 0 0 0 3 56
    P Name2 0 0 0 0 54 67 0 0 3 56 0 0 0 0
    P Name8 0 0 0 0 3 233 65 548 0 0 0 0 0 0
    P Name7 0 0 0 0 0 0 3 6 0 0 0 0 0 0
    P Name9 0 0 0 0 0 0 0 0 0 0 0 0 65 87

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

    Please Help.

    Thanks

    Monday, July 16, 2018 5:35 AM

Answers

  • User77042963 posted
    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 RType,Client
     ,ISNULL(max(Case when [Month]=1 then TRequest else null end ),0) [Jan_TRequest]
     ,ISNULL(max(Case when [Month]=1 then Due else null end ),0) [Jan_Due]
     ,ISNULL(max(Case when [Month]=2 then TRequest else null end ),0) [Feb_TRequest]
     ,ISNULL(max(Case when [Month]=2 then Due else null end ),0) [Feb_Due]
    
     ---....
     ,ISNULL(max(Case when [Month]=7 then TRequest else null end ),0) [July_TRequest]
     ,ISNULL(max(Case when [Month]=7 then Due else null end ),0) [July_Due]
    --...
    
      ,ISNULL(max(Case when [Month]=12 then TRequest else null end ),0) [Dec_TRequest]
     ,ISNULL(max(Case when [Month]=12 then Due else null end ),0) [Dec_Due]
    
     FROM #TmpData
     Group By  RType,Client
      Order by Client
    
    
     drop table #TmpData

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 16, 2018 1:55 PM