PIVOT and UNPIVOT in SQL

Question

help me out in solving this problem. my table is like below. UniqueId ActivityName 111 activity1 112 activity1 111 activity2 113 activity2 i want the result like below using sql pivoting query 2016 UniqueId activityname activity1 activity2 111 activity1 yes yes 112 activity1 yes No 113 activity2 No yes

Jenith Thakkar · 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

Aman Gupta · Answer

Using this Syntax and apply your problem statement. SELECT , [first pivoted column] AS , [second pivoted column] AS , ... [last pivoted column] AS FROM ( ) AS PIVOT ( ( ) FOR [ ] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS ;

Anju D · Answer

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 Thakkar · Answer

Hi so what kind of issue you are facing?

Anju D · Answer

@jenith Thakkar yes its working. but problem still exists. i have table like below UniqueId activity name status CompletedDate 111 activity1 Completed 28-Jul-2020 112 activity2 Completed 30-jul-2020 111 activity2 Not Completed 31-jul-2020 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

Anju D · Answer

thank you so much, it worked for me but unique id's are coming duplicate with respect to activities. 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 at last we have to group by UniqueId.

Jenith Thakkar · Answer

Is it working?

Anju D · Answer

Yes i want the answer as you shown it. no need to display activity name column. uniqueID Activity1 Activity2 111 Yes Yes 112 Yes No

Jenith Thakkar · Answer

Hi There's two value in 111 but as per your expected output you want only activity1 show in activity name? You want to show activity name in expacted output or just like uniqueID Activity1 Activity2 111 Yes Yes 112 Yes No

Jenith Thakkar · Answer

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

UniqueId	ActivityName
111	activity1
112	activity1
111	activity2
113	activity2

PIVOT and UNPIVOT in SQL

Answers (10)