How can i group multiple rows with case statement

kiran kumar

8y1k

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

4 Replies

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

kiran kumar8y ago
Patel thank you for your replay... however PIVOT also not giving the output as i expect
B Patel8y ago
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 kumar8y ago
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
Kartik Pawar8y ago
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.