• 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