How to pivot on multiple columns
I am try to pivot the following data
Normalized data
USI Review Status
9 A A
9 B A
9 C B
9 D A
9 E D
9 F B
9 G H
9 H E
So that I get the following result
Pivoted Output
USI Review1 Status1 Review2 Status2 Review3 Status3
9 A A B A C B
I have written the following code
SELECT USI, [FRS] AS Review1, [Loss Mitigation Decisioning]AS Review2, [Loss Mitigation Handling] AS Review3
FROM
(SELECT USI, Review
FROM dbo.vwAllReviewStatus
) AS SourceTable
PIVOT
(
max(Review)
FOR [Review] IN ([FRS], [Loss Mitigation Decisioning], [Loss Mitigation Handling])
) AS P1
Which gives me the following result
USI Review1 Review2 Review3
9 A B C
I need some help in getting the status1, status2, and status3 fields in
Thanks
Normalized data
USI Review Status
9 A A
9 B A
9 C B
9 D A
9 E D
9 F B
9 G H
9 H E
So that I get the following result
Pivoted Output
USI Review1 Status1 Review2 Status2 Review3 Status3
9 A A B A C B
I have written the following code
SELECT USI, [FRS] AS Review1, [Loss Mitigation Decisioning]AS Review2, [Loss Mitigation Handling] AS Review3
FROM
(SELECT USI, Review
FROM dbo.vwAllReviewStatus
) AS SourceTable
PIVOT
(
max(Review)
FOR [Review] IN ([FRS], [Loss Mitigation Decisioning], [Loss Mitigation Handling])
) AS P1
Which gives me the following result
USI Review1 Review2 Review3
9 A B C
I need some help in getting the status1, status2, and status3 fields in
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Part of the problem was that
max(r1) as Review1, max(s1) as Status1
, max(r2) as Review2, max(s1) as Status2
, max(r3) as Review3, max(s1) as Status3
Should be
max(r1) as Review1, max(s1) as Status1
, max(r2) as Review2, max(s2) as Status2
, max(r3) as Review3, max(s3) as Status3
max(r1) as Review1, max(s1) as Status1
, max(r2) as Review2, max(s1) as Status2
, max(r3) as Review3, max(s1) as Status3
Should be
max(r1) as Review1, max(s1) as Status1
, max(r2) as Review2, max(s2) as Status2
, max(r3) as Review3, max(s3) as Status3
Good catch--copy/paste error on my part. Let me know if there are other issues.
ASKER
Hello dqmq,
The code now works but I have some questions as to how it works. In particular the following segment. I understand the use of the Case when statement but not of "end as S1". Could you clarify that for me.
Thanks
Select USI
, case when review='A' then review end as R1
, case when review='A' then status end as S1
, case when review='B' then review end as R2
, case when review='B' then status end as S2
, case when review='C' then review end as R3
, case when review='C' then status end as S3
The code now works but I have some questions as to how it works. In particular the following segment. I understand the use of the Case when statement but not of "end as S1". Could you clarify that for me.
Thanks
Select USI
, case when review='A' then review end as R1
, case when review='A' then status end as S1
, case when review='B' then review end as R2
, case when review='B' then status end as S2
, case when review='C' then review end as R3
, case when review='C' then status end as S3
ASKER
I understand
"case when review='A' then review end as R1 "
but I am trying to figure out how
" case when review='A' then status end as S1 " is returning the correct status. I don't really want to implement code without understanding how it works.
"case when review='A' then review end as R1 "
but I am trying to figure out how
" case when review='A' then status end as S1 " is returning the correct status. I don't really want to implement code without understanding how it works.
It will return the correct status if your values in the Review coulumn are distinct for any given USI value, i.e. only one each of A, B, C, D, E, F, G, H for USI=9.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I decided to split points. Both solutions worked. But after bench testing both solutions, I found that dqmq's solution executed much faster and so I chose that solution. However, cgLutteral showed me how to pivot using multiple pivots which I found very informative.
ASKER
I tried your code but the status stayed the same for all reviews
For the values of s1, s2, s3 I was expective A, A, B. I got A ,A, A instead