Get counts in to single row where data occurence exist in multiple rows

Question

Hi Experts
I have the data as below and trying to find an easy way of getting the expected result.

One row per WOID and Measure counts based on Category values.

Appreciate your help.

Declare @Sample Table(ID int, WOID int, Category char(1), Description varchar(20)); 
Insert @Sample(ID, WOID, Category, Description) Values 
(1, 1234, 'A', 'Something 1'), 
(2, 1234, 'B', 'Something 2'), 
(3, 1234, 'C', 'Something 3'), 
(4, 1234, 'D', 'Something 4'), 
(5, 1299, 'A', 'Something 5'), 
(6, 1299, 'B', 'Something 6'), 
(7, 1299, 'C', 'Something 1'), 
(8, 1200, 'A', 'Something 1'), 
(9, 1200, 'B', 'Something 7'), 
(10, 1200, 'D', 'Something 8');

Expected result as below

Answer 1

Hi @Shyam Sreeramula

One row per WOID and Measure counts based on Category values.

Then WOIDCount will always be '1' for each row right?

If I understood right, then in Viorel's answer, it is no need to count(WOID) as WOIDCount , just 1 as WOIDCount will be fine.

Also, you could try this query using PIVOT:

SELECT WOID,
       1 AS WOIDCount,
	   [A] AS ACount,
	   [B] AS BCount,
	   [C] AS CCount,
	   [D] AS DCount
FROM (SELECT WOID,Category,Description FROM @Sample)S
PIVOT(COUNT(Description) FOR Category IN([A],[B],[C],[D]))P

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check one of queries:

select WOID, 
	count(WOID) as WOIDCount, 
	count(case Category when 'A' then 0 end) as ACount,
	count(case Category when 'B' then 0 end) as BCount,
	count(case Category when 'C' then 0 end) as CCount,
	count(case Category when 'D' then 0 end) as DCount
from @Sample
group by WOID
order by WOID