SQL Grouping in Pivot table

Question

I have a created a pivot table but the data are not getting grouped together. Can anyone help me in getting the desired format?

The query i have written to get the pivot table:

DECLARE @cols   AS NVARCHAR(MAX),   -- for pivot
     @cols2  AS NVARCHAR(MAX),   -- for select
     @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover]) 
            FROM #cover2 c 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- this is for the SELECT
SET @cols2 = STUFF((SELECT  ',' + 'ISNULL(' + QUOTENAME(c.[Offer_cover]) + ', 0) ' + QUOTENAME(c.[Offer_cover])
            FROM #cover2 c 
            GROUP BY c.[Offer_cover]  -- changes here
            ORDER BY c.[Offer_cover]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Ref,offer_cover_id, ' + @cols2 + ' from 
            (
                select *
                from #cover2 
            ) x 
            pivot 
            (
                 SUM(cover_earning_Count)
                 for [Offer_cover] in (' + @cols + ') 
            ) p' 


execute( @query)

Actual Result:

Ref             offer_cover_id  6667    13333   20000   26667   33333
42186_43252      1               0      0       0       0       1
42186_43252      1               0      0       0       0       0
42186_43252      1               1      0       0       0       0
42186_43252      1               0      1       0       0       0
42186_43252      1               0      0       0       2       0
42186_43252      1               0      0       0       0       0
42186_43252      1               0      0       0       0       0
42217_43252      1               0      1       0       0       0
42217_43252      1               0      0       1       0       0
42217_43252      1               0      0       0       0       0
42217_43252      1               0      0       0       0       0
42217_43252      1               0      0       0       0       0
42217_43252      1               0      0       0       0       0
42217_43252      1               0      0       0       0       0
42217_43252      1               0      0       0       0       0
42217_43252      1               0      0       0       0       0
42217_43252      1               0      0       0       0       0

Desired Output:

Ref offer_cover_id  6667    13333   20000   26667   33333
42186_43252 1        1        1       0       2       1
42217_43252 1        0        1       1       0       0

Answer 1

Any columns passed into the PIVOT and not aggregated will be part of the grouping, so since you are doing have

from 
(
    select *
    from #cover2 
) x 

You will be grouping by all of the columns in #cover except for cover_earning_Count and Offer_cover, the solution is to only select the columns you need (which is a pretty good rule of thumb anyway):

set @query = 'SELECT Ref,offer_cover_id, ' + @cols2 + ' from 
            (
                select  Ref,offer_cover_id, cover_earning_Count, Offer_cover
                from #cover2 
            ) x 
            pivot 
            (
                 SUM(cover_earning_Count)
                 for [Offer_cover] in (' + @cols + ') 
            ) p' 

As an aside, you can set both of your column variables in a single statement. I don't think it will add much of a performance gain, but every little helps:

SELECT  @Cols = STUFF(x.Cols.query('Col1').value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        @Cols2 = STUFF(x.Cols.query('Col2').value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    (
            SELECT Col1 = ',' + QUOTENAME(c.[Offer_cover]),
                Col2 = ',ISNULL(' + QUOTENAME(c.[Offer_cover]) + ', 0) ' + QUOTENAME(c.[Offer_cover])
            FROM #cover2 c 
            GROUP BY c.[Offer_cover]  -- changes here
            ORDER BY c.[Offer_cover]
            FOR XML PATH(''), TYPE
        ) x (Cols);

Answer 2

You need to change the query in the x subquery.

Because with a select * you drag also the other fields from that table in it.
Some of it which aren't unique per (ref, offer_cover_id) tupple.

So change it to:

select Ref, offer_cover_id, Offer_cover, cover_earning_Count
from #cover2