question

MH88 avatar image
MH88 asked

How to get a single row for each record?

I converted rows to column via below query select LAST_MOD_BY,(select count(*) from tblALRT where LAST_MOD_BY='USER2' )'Total Cases Worked', (case when STATUS_TYPE = 'Pending' then count(*) end) 'Pending' , (case when STATUS_TYPE = 'Reassign' then count(*) end) 'Reassign'', (case when STATUS_TYPE = 'Lost' then count(*) end) 'Lost' from tblALRT where LAST_MOD_BY='USER2' group by STATUS_TYPE,LAST_MOD_BY The result is: LAST_MOD_BY Total Cases Worked Pending Reassign Lost USER2 3 1 null null USER2 3 null 1 null User2 3 null null 1 I want the following result LAST_MOD_BY Total Cases Worked Pending Reassign Lost USER2 3 1 1 1 Please help. Thanks
querysql-server-2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mdawini avatar image
mdawini answered
`Try the below. It might not scale but will solve your problem. You can also try pivoting as well. Remove the comment lines in the filter so you get your USER2. IF OBJECT_ID('TempDB..#tblALRT') IS NOT NULL DROP TABLE #tblALRT GO CREATE TABLE #tblALRT( LAST_MOD_BY NVARCHAR(20), STATUS_TYPE NVARCHAR(20) ) INSERT INTO #tblALRT(LAST_MOD_BY, STATUS_TYPE) VALUES('USER2','Pending'), ('USER2','Reassign'), ('USER2','Lost'), ('USER3','Pending'), ('USER3','Reassign'), ('USER3','Lost'),('USER3','Lost'),('USER3','Pending') SELECT LAST_MOD_BY, COUNT(LAST_MOD_BY) AS 'Total Cases Worked', SUM(COALESCE((CASE WHEN STATUS_TYPE = 'Pending' THEN 1 END),0)) AS 'Pending', SUM(COALESCE((CASE WHEN STATUS_TYPE = 'Reassign' THEN 1 END),0)) AS 'Reassign', SUM(COALESCE((CASE WHEN STATUS_TYPE = 'Lost' THEN 1 END),0)) AS 'Lost' FROM #tblALRT --WHERE LAST_MOD_BY='USER2' GROUP BY LAST_MOD_BY
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks! It worked.
0 Likes 0 ·
mdawini avatar image
mdawini answered
![alt text][1] [1]: /storage/temp/4169-users.png

users.png (28.0 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.