locked
Pivot for 2 columns RRS feed

  • Question

  • I want to do pivot for multiple columns.So i have written the below code.

    IS there any other way to write this Query so that we can get rid of this join between 2 pivots.

    select p.LoanID,p.[1] as commit1,p1.[1] as cqrvalue1 ,
    
    p.[2] as commit1,p1.[2] as cqrvalue2 ,
    
    p.[3] as commit1,p1.[3] as cqrvalue3 ,
    
    p.[4] as commit1,p1.[4] as cqrvalue1 
    
    from (
    
    (SELECT [CQRSplitID]
    
          ,ISNULL([CommitmentAmt],0) as commitm
    
          ,[LoanID]
    
      FROM [Staging].[QSRLoanCQRSplit] a
    
      left join Staging.WCRECQRVALUE b
    
      on a.CQRID=b.CQRID) as src
    
      PIVOT 
    
      ( sum(commitm) 
    
      for CQRSplitID IN ([1], [2], [3], [4])) as P
    
      inner join
    
      (SELECT [CQRSplitID]
    
          ,ISNULL([CQRValue],0) as cqrvalue
    
          ,[LoanID]
    
      FROM [Staging].[QSRLoanCQRSplit] a
    
      left join Staging.WCRECQRVALUE b
    
      on a.CQRID=b.CQRID) as src
    
      PIVOT 
    
      ( sum(cqrvalue) 
    
      for CQRSplitID IN ([1], [2], [3], [4])) as P1
    
      on p1.LoanID=p.LoanID)
    
    
    
    
    
    
    
    
    
    Thanks in Advance..

    Thursday, October 27, 2011 6:42 AM

Answers

  • select LoanID 
         , sum(case when CQRSplitID = 1 then isnull( [CommitmentAmt], 0 ) else 0 end )  commit1
         , sum(case when CQRSplitID = 1 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue1
         , sum(case when CQRSplitID = 2 then isnull( [CommitmentAmt],0 ) else 0 end )  commit2
         , sum(case when CQRSplitID = 2 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue2
         , sum(case when CQRSplitID = 3 then isnull( [CommitmentAmt],0 ) else 0 end )  commit3
         , sum(case when CQRSplitID = 3 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue3
         , sum(case when CQRSplitID = 4 then isnull( [CommitmentAmt],0 ) else 0 end )  commit4
         , sum(case when CQRSplitID = 4 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue4
      from [Staging].[QSRLoanCQRSplit] a
        left join Staging.WCRECQRVALUE b
          on a.CQRID = b.CQRID 
      group by LoanID
    


    http://www.t-sql.ru
    Thursday, October 27, 2011 7:13 AM

All replies

  • Hi Vani,

                  please post the sample data for the better understanding .Please talk something about what exactly u r trying to acheive

    and what is the problem u r facing with the query that u posted :)

     

    Regards

    Chaithu :)

    Thursday, October 27, 2011 7:09 AM
  • select LoanID 
         , sum(case when CQRSplitID = 1 then isnull( [CommitmentAmt], 0 ) else 0 end )  commit1
         , sum(case when CQRSplitID = 1 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue1
         , sum(case when CQRSplitID = 2 then isnull( [CommitmentAmt],0 ) else 0 end )  commit2
         , sum(case when CQRSplitID = 2 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue2
         , sum(case when CQRSplitID = 3 then isnull( [CommitmentAmt],0 ) else 0 end )  commit3
         , sum(case when CQRSplitID = 3 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue3
         , sum(case when CQRSplitID = 4 then isnull( [CommitmentAmt],0 ) else 0 end )  commit4
         , sum(case when CQRSplitID = 4 then isnull( [CQRValue], 0 ) else 0 end ) cqrvalue4
      from [Staging].[QSRLoanCQRSplit] a
        left join Staging.WCRECQRVALUE b
          on a.CQRID = b.CQRID 
      group by LoanID
    


    http://www.t-sql.ru
    Thursday, October 27, 2011 7:13 AM