Select unique values as columns and count or their matches each unique value of a specific column

Question

I have a table with four columns: Id , Name, CampaignId, Type.

Here is a sample:

+----+-----------+------------+------+
| Id |   NAME    | CampaignId | Type |
+----+-----------+------------+------+
|  1 | Campaign1 |         10 |    0 |
|  2 | Campaign2 |         11 |    0 |
|  3 | Campaign2 |         11 |    1 |
|  4 | Campaign2 |         11 |    2 |
|  5 | Campaign2 |         11 |    3 |
|  6 | Campaign3 |         12 |    1 |
|  7 | Campaign3 |         12 |    2 |
|  8 | Campaign3 |         12 |    2 |
|  9 | Campaign3 |         12 |    2 |
| 10 | Campaign4 |         13 |    0 |
| 11 | Campaign4 |         13 |    2 |
+----+-----------+------------+------+

DROP TABLE IF EXISTS #TMP;
CREATE TABLE #TMP (
    [Id] INT IDENTITY ,
    [NAME] NVARCHAR(20),
    [CampaignId] INT,
    [Type] INT
);

INSERT INTO #TMP ([Name], [CampaignId], [Type]) VALUES 
('Campaign1', 10, 0),
('Campaign2', 11, 0),
('Campaign2', 11, 1),
('Campaign2', 11, 2),
('Campaign2', 11, 3),
('Campaign3', 12, 1),
('Campaign3', 12, 2),
('Campaign3', 12, 2),
('Campaign3', 12, 2),
('Campaign4', 13, 0),
('Campaign4', 13, 2)

SELECT * FROM #TMP

I need a query to create a new table that will output a row that contains the Name, CampaignId and columns for each unique value in Type with its corresponding count for each unique CampaignId.

I don't know the exact number and possible values in Type column. It could be vary.

Expected result for sample dataset is a table

+-----------+------------+-------+-------+-------+-------+
|   Name    | CampaignId | Type0 | Type1 | Type2 | Type3 |
+-----------+------------+-------+-------+-------+-------+
| Campaign1 |         10 |     1 |     0 |     0 |     0 |
| Campaign2 |         11 |     1 |     1 |     1 |     1 |
| Campaign3 |         12 |     0 |     1 |     3 |     0 |
| Campaign4 |         13 |     1 |     0 |     1 |     0 |
+-----------+------------+-------+-------+-------+-------+

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Serg, Jun 21 '19 at 09:48
Are you limited to Types 0-3, or could there be (a lot?) more? Could it vary? — Thom A, Jun 21 '19 at 09:50
I dont know the exact number of different types. It could be vary. — Andrey Dengin, Jun 21 '19 at 10:51

score 0 · Answer 1 · answered Jun 21 '19 at 09:45

use conditional agregation

selecr name,CampaignId,sum(case when  type =0 then 1 else 0 end) as type0,
sum(case when  type =1 then 1 else 0 end) as type1,
sum(case when  type =2 then 1 else 0 end) as type2,
sum(case when  type =3 then 1 else 0 end) as type3 from #TMP
group by name,CampaignId

score 0 · Answer 2 · answered Jun 21 '19 at 09:49

Try this:

SELECT name,
       campaignid,
       Type0=sum(case when type= 0 then 1 else 0 END ) ,
       Type1=sum(case when type= 1 then 1 else 0 END ) ,
       Type2=sum(case when type= 2 then 1 else 0 END ) ,
       Type3=sum(case when type= 3 then 1 else 0 END ) 
       FROM #TMP
       group by name,campaignid

Select unique values as columns and count or their matches each unique value of a specific column

2 Answers2