Something more like this maybe:
use SandBox; -- Move to a SandBox database for testing
go
create table dbo.ConversionsTest (
ActDate date,
AdId int,
ActivityTagID int,
PostImpConversions int,
PostClickConversions int
);
insert into dbo.ConversionsTest
values
('2013-04-1', 12807324, 280378, 0, 1),
('2013-04-1', 13573240, 280378, 0, 1),
('2013-04-1', 13575149, 280378, 0, 3),
('2013-04-1', 13575471, 280378, 0, 2),
('2013-04-1', 13521989, 280377, 0, 1),
('2013-04-1', 13521989, 280378, 0, 3),
('2013-04-1', 13521989, 289201, 0, 1),
('2013-04-1', 13566439, 280378, 0, 1),
('2013-04-1', 13566442, 280378, 0, 1),
('2013-04-1', 13597657, 280378, 0, 1);
go
DECLARE @PivotColumnHeaders VARCHAR(MAX);
DECLARE @PivotColumnHeaders1 VARCHAR(MAX);
with DistinctActivityTagIDs as (
select distinct ActivityTagID from dbo.ConversionsTest)
SELECT
@PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast([ActivityTagID] as varchar) + ']',
'[' + cast([ActivityTagID] as varchar)+ ']'
),
@PivotColumnHeaders1 =
COALESCE(
@PivotColumnHeaders1 + ',isnull([' + cast([ActivityTagID] as varchar) + '],0) as [' + cast([ActivityTagID] as varchar) + ']',
'isnull([' + cast([ActivityTagID] as varchar) + '],0) as [' + cast([ActivityTagID] as varchar) + ']'
)
FROM
DistinctActivityTagIDs
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT [ActDate],[AdId],' + @PivotColumnHeaders1 + N'
FROM (
select [ActDate],[AdId],[ActivityTagID],[PostClickConversions]
from dbo.ConversionsTest
) AS PivotData
PIVOT (
sum([PostClickConversions])
FOR [ActivityTagID] IN (' + @PivotColumnHeaders + '
)
) AS PivotTable
'
EXECUTE(@PivotTableSQL)
go
drop table dbo.ConversionsTest;
go