How to collapse multiple rows into one on a pivot table

Question

I am relatively inexperienced in SQL code so here goes.

I would like to pull data from a table and use PIVOT to turn values into column headings.

I have managed to achieve what I want using the PIVOT command (and dynamic field names) but I was hoping that all values against a single ref would appear on one line. However I get one line for each code with a value in the original table (see the results below).

Using CROSS APPLY seems to be the way to go (it's commented out in my code sample) but I have hit a few problems with it:

I cannot fathom how it works, so adapting online examples to my scenario has involved some guess work.
I entered (what I think are) the correct references within the command according to another post on this site here: Pivot a table on a value but group the data on one line by another? where they claimed that both values within VALUES need to be the same datatype for the unpivoting process to work. However setting 'value' to varchar(10) stops the SUM function from working within the PIVOT statement. Running it as decimal/float produces all NULL results.
With 'value' set decimal including the GROUP BY statement does produce one line of data, however (as in point 2) all code values are NULL.

Hope all this makes sense. Please advice how I can collapse the results below into one line. Thanks in advance.

DECLARE @CODES nvarchar(max)
select @CODES =
    stuff(
    (
    select distinct ',[' + code + ']'
    from codetable
    for xml path('')
    ),
    1,1,'')

DECLARE @SQL nvarchar(max)
SET @SQL = 
N'
SELECT
    ref,
    ' + @CODES + ',
FROM
    codetable
    --CROSS APPLY
    --(
 --   VALUES
 --     (''code'', convert(varchar(10), code, 120)),
 --     (''value'', convert(decimal, value))
    --) CA (CODE, VAL)
PIVOT(SUM(value)
    FOR code IN (' + @CODES + '))
    AS PVTTable2
WHERE
    ref = ''101'' AND end_date IS NULL
GROUP BY ref,' + @CODES + '
'
exec sp_executesql @SQL

My original table is like this:
-------------------------
|ref    |CODES  |VALUE  |
-------------------------
|101    |CODE4  |20     |
|101    |CODE1  |2      |
|101    |CODE7  |38     |


The results I get are:
-------------------------------------------------------------------------
|ref    |CODE1  |CODE2  |CODE3  |CODE4  |CODE5  |CODE6  |CODE7  |CODE8  |
-------------------------------------------------------------------------
|101    |NULL   |NULL   |NULL   |20     |NULL   |NULL   |NULL   |NULL   |
|101    |2      |NULL   |NULL   |NULL   |NULL   |NULL   |NULL   |NULL   |
|101    |NULL   |NULL   |NULL   |NULL   |NULL   |NULL   |38     |NULL   |

Hi, Running: `select * from ( ) GROUP BY ref` gives me `Incorrect syntax near the keyword 'GROUP'.` — Chris Reid, Oct 04 '19 at 07:46

score 0 · Answer 1 · answered Oct 03 '19 at 16:53

GROUP BY ref only

DECLARE @CODES nvarchar(max)
select @CODES =
    stuff(
    (
    select distinct ',[' + code + ']'
    from codetable
    for xml path('')
    ),
    1,1,'');

DECLARE @CODESGrp nvarchar(max)
select @CODESGrp =
    stuff(
    (
    select distinct ',min([' + code + '])'
    from codetable
    for xml path('')
    ),
    1,1,''); 

DECLARE @SQL nvarchar(max);
SET @SQL = 'SELECT  ref,' + @CODESGrp 
+ ' FROM  codetable '
+ ' PIVOT(SUM(value) FOR code IN (' + @CODES + ')) AS PVTTable2'
+ ' WHERE ref = 101 GROUP BY ref' ;

exec sp_executesql @SQL;

Thanks for the response. Deepshikha's solution worked for me but I will come back to your's if I need to revise the code again. — Chris Reid, Oct 04 '19 at 08:49

score 0 · Accepted Answer · answered Oct 03 '19 at 19:31

Pivot should be written as:

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(T.CODES) 
            FROM codetable T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ref, ' + @cols + ' from 
            (
             SELECT
                 ref, --< grouping column >,
                 CODES, --< spreading column >, 
                 VALUE --< aggregation column >
             FROM codetable
           ) PivotData
            pivot 
            (
                sum(VALUE)
                for CODES in (' + @cols + ')
            ) p '

exec sp_executesql @query

sample code here..

How to collapse multiple rows into one on a pivot table

2 Answers2