locked
Pivot for 2 columns RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote 
    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