0

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 |
+-----------+------------+-------+-------+-------+-------+
Andrey Dengin
  • 181
  • 2
  • 15

2 Answers2

0

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
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

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
Red Devil
  • 2,343
  • 2
  • 21
  • 41