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 | +-----------+------------+-------+-------+-------+-------+