PIVOT and UNPIVOT in SQL

Anju D

6y926

help me out in solving this problem. my table is like below.

i want the result like below using sql pivoting query 2016

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar6y ago
Accepted answer
Okay
SELECT UniqueId,
       CASE WHEN [activity1] IS NOT NULL THEN 'Yes' ELSE 'No' END [activity1],
       CASE WHEN [activity2] IS NOT NULL THEN 'Yes' ELSE 'No' END [activity2]
FROM   [Activity]
       PIVOT (Max([ActivityName])
             FOR [ActivityName] IN ([activity1],[activity2])) piv
It will work for you

Please accept the answer if it will helpful for you
Thank you
Jenith Thakkar6y ago
Hi
Try this
select UniqueId,
  MAX(CASE WHEN ActivityName='activity1' THEN 'Yes' ELSE 'No' END) as activity1,
  MAX(CASE WHEN ActivityName='activity2' THEN 'Yes' ELSE 'No' END) as activity2,
  MAX(CASE WHEN ActivityName='activity1' THEN Status ELSE Null END) as activity1_Status,
  MAX(CASE WHEN ActivityName='activity2' THEN Status ELSE Null END) as activity2_Status,
  MAX(CASE WHEN ActivityName='activity1' THEN CompleteDate ELSE Null END) as activity1_CompletedDate,
  MAX(CASE WHEN ActivityName='activity2' THEN CompleteDate ELSE Null END) as activity2_CompletedDate
FROM Activity
GROUP BY UniqueId
Anju D6y ago
as i posted the table data, completed date column is dynamic, so how can i display with respect to activities status and completed date in 2 different coulumns .
Jenith Thakkar6y ago
Hi so what kind of issue you are facing?

@jenith Thakkar

yes its working. but problem still exists. i have table like below

and the result should be like below table

uniqueId	activity1	activity1_Status	activity1_CompletedDate	activity2	activity2_status	activity2_CompletedDate
111	yes	Completed	28-jul-2020	yes	Not Completed	31-jul-2020
112	No	NA	NA	Yes	Completed	30-jul-2020