How can i group multiple rows with case statement

Question

Hi Friends, I i have table like below ; i need to group by CWID and RequestNo and based on Type (Email or PC) corresponding status should capture in two(Emailstatus and PC status) new columns. Kindly let me know the query if you know please. REQUESTNO CWID Type Status Request 1 ABC Email Completed Request 1 ABC PC WIP Request 2 ABC Email WIP Request 2 XYZ PC Completed Request 2 XYZ Email Completed I need out put like this Request No CWID Email status PC status Request 1 ABC Completed WIP Request 2 ABC WIP N/A Request 2 XYZ Completed Completed Thanks in advance, Kiran

B Patel · Answer

You can solve this by Case and pivot table option -- table creation create table abc ( requestno nvarchar(max), CWID Nvarchar(max), Type nvarchar(max), status nvarchar(max) ) ---insert records insert into abc (requestno,CWID,Type,status ) values('Request 1','ABC','Email','Completed') insert into abc (requestno,CWID,Type,status ) values('Request 1','ABC','PC','WIP') insert into abc (requestno,CWID,Type,status ) values('Request 2','xyz','Email','Completed') insert into abc (requestno,CWID,Type,status ) values('Request 1','ABC','Email','Completed') insert into abc (requestno,CWID,Type,status ) values('Request 1','ABC','Email','Completed') ---pivot table query select * from abc pivot(min(status) for type in( Email,PC)) as MrStark Perfect!!! Get the outpur as per You said. thanks !! reader

kiran kumar · Answer

I tried below query but its not giving as i expected output SELECT Requestno,CWID, CASE WHEN Type= 'Email' THEN Status else 'NA' END as EmailStatus , CASE WHEN TaskType= 'PC' THEN Status else 'NA' END as PCstatus FROM [maintbl] GROUP BY Requestno, cwid, CASE WHEN Type= 'Email' THEN Status else 'NA' END , CASE WHEN TaskType= 'PC' THEN Status else 'NA' END

kiran kumar · Answer

Patel thank you for your replay... however PIVOT also not giving the output as i expect

Kartik Pawar · Answer

Hello, You can use CASE Syntax with Group By Clause.First you have to select the columns for displaying then, use CASE Syntax later group it with corresponding columns. I hope you got some idea about how to do it.

REQUESTNO	CWID	Type	Status
Request 1	ABC	Email	Completed
Request 1	ABC	PC	WIP
Request 2	ABC	Email	WIP
Request 2	XYZ	PC	Completed
Request 2	XYZ	Email	Completed

Request No	CWID	Email status	PC status
Request 1	ABC	Completed	WIP
Request 2	ABC	WIP	N/A
Request 2	XYZ	Completed	Completed

How can i group multiple rows with case statement

Answers (4)