How to do a pivot when you don't know the values?

Question

Given table Fruit

 id     food       color      price
 -----  -------    -------    ----
 1      cherry     red        0.23 
 2      apple      red        0.65 
 3      apple      green      0.77
 4      orange     orange     1.03
 5      lemon      yellow     1.45
 6      grape      green      0.10
 7      grape      purple     0.11
 8      plum       purple     0.94

SELECT color, count(id) AS 'tot' FROM Fruit GROUP BY color

yields

color  tot
------ ---
green  2
orange 1
purple 2
red    2
yellow 1

this gets me close

SELECT * FROM
( 
    SELECT color, count(id) AS 'tot' FROM Fruit GROUP BY color
) src
pivot
(
    SUM('tot')
    FOR color IN ([green],[orange],[purple],[red],[yellow])
) piv

yields

green orange purple red yellow
    2      1      2   2      1

but how to add a row total? Maybe an UNPIVOT ?

green orange purple red yellow total
    2      1      2   2      1     8

and for the bonus round, how to achieve a 2D array? like

           green orange purple red yellow total
cherry                           1            1
apple          1                 1            2
orange                1                       1 
lemon                                   1     1
grape          1             1                2
plum                         1                1
total          2      1      2   2      1     8

And of course this only works if you have a limited, known list to hardcode.
Seems like a stored procedure with some parameters would be needed for when you don't know the colors in advance.

Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, May 18 '23 at 03:04
Thats normally a task for your presentation layer... you don't normally get your database to gerenate it. — Dale K, May 18 '23 at 03:05
And please use a meaningful title - that coveys something useful to the reader. — Dale K, May 18 '23 at 03:05
Conditional aggregation makes it much easier to do a total column as then you just `COUNT(*)`. — Thom A, May 18 '23 at 03:07

score 0 · Answer 1 · answered May 22 '23 at 09:08

You can use dynamic SQL for this purpose. Of course it has a symbols limit for exec. But maybe it will give you an idea how to do it.

if object_id('#tmp') is not null
    drop table #tmp 
create table #tmp(food varchar(255), color varchar(255))
go
if object_id('tempdb..#result') is not null  
    drop table #result
go
declare @sql nvarchar(max) = ''
        /*number of unique colors for final columns*/
        , @columns nvarchar(max) = stuff(isnull((select ', ['+ color +'] int'
                                                from (select distinct color from Fruit) clr
                                                order by color
                                                for xml path('')),''),1,2,'')
        /*total sums for each color column*/
        , @summary nvarchar(max) = stuff(isnull((select ', sum(['+ color +']) as ['+ color +']'
                                                from (select distinct color from Fruit) clr
                                                order by 1
                                                for xml path('')),''),1,2,'')
        /*total sums for each food*/
        , @totals  nvarchar(max) = stuff(isnull((select '+ ['+ color +']'
                                                from (select distinct color from Fruit) clr
                                                order by 1
                                                for xml path('')),''),1,2,'')

--turn colors into columns and add total column for each row
insert into #tmp 
select distinct food, 
stuff(isnull((select ', '+cast(iif(clr.color=org.color,1,0) as varchar)+' as ['+ color +']'
            from (select distinct color from Fruit) clr
            order by clr.color
            for xml path('')),''),1,2,'')+', 1 as total'
from Fruit org

--create final table according to number of columns from @columns
set @sql = N'create table #result(food varchar(255),'+@columns+', totals int); '+char(10)+
            'insert into #result '+char(10);

select @sql = @sql + N'select '''+food+''' as food, '+color+' union all '+char(10) from #tmp;

--remove last 'union all'
set @sql = reverse(stuff(reverse(@sql),1,11,''));

--final result with totals
set @sql = @sql + N'select food, '+@summary+', sum('+@totals+') as [total_column] from #result group by food'+char(10)+
                    'union all '+char(10)+
                    'select ''total_row'', '+@summary+', sum(totals) as [total_column] from #result';

exec(@sql)

In my SQL Server version there is no STRING_AGG(). If your SQL Server is 2017 or higher you can simplify stuff/xml statements.

This is how the result looks like:

How to do a pivot when you don't know the values?

1 Answers1