Using group by but generate several columns

Question

I'd like to get something a little bit strange.

Let me start with what I want:

day Code0 Code1 ... ... CodeN

01/01/2016 20 23 3

02/01/2016 16 18 7

etc

Given that I have a table Codes this is what I have so far:

select [day], SUM(value) as Total, code into #tablaTemporal1
from Codes
group by [day], code

Which gives me:

day Total Code

01/01/2016 20 Code0

01/01/2016 23 Code1

01/01/2016 3 CodeN

02/01/2016 16 Code0

02/01/2016 18 Code1

02/01/2016 7 CodeN

etc

It's important to say that the number of different codes N is limitless; and by limitless I mean that they might change.

Is there any way to instead of getting a row for each day and code I get a single row per day but giving me the total for each code in the columns?

Dynamic pivoting is much cleaner and easier in the display layer (report, app, web page, etc.). SQL is not designed for dynamic pivoting since it generally likes to have a fixed set of columns, — D Stanley, Jun 16 '16 at 16:49

score 0 · Answer 1 · answered Jun 16 '16 at 15:38

0

For a predefined set of Code values you can use a query like this:

select [day], 
       SUM(CASE WHEN Code = 'Code0' THEN value END) as Code0, 
       SUM(CASE WHEN Code = 'Code1' THEN value END) as Code1, 
       ...
       SUM(CASE WHEN Code = 'CodeN' THEN value END) as CodeN
from Codes
group by [day]

answered Jun 16 '16 at 15:38

Giorgos Betsos

71,379
9
63
98

It's not predefined :( I tried that as well when I thought they were limited but I saw today that there are some new ones so N should be considered limitless – Miquel Coll Jun 16 '16 at 15:41
2

Then you need a dinamic pivot. http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Juan Carlos Oropeza Jun 16 '16 at 15:42

Tudor Saru · Answer 2 · 2016-06-16T17:28:14.013

dynamically put all the agregate columns into a variable @cols and the assumption I made is that your temp table is named #t. If you need a real table you'll have to change the inner query to something like

SELECT name FROM YourDatabaseName.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'yourTableName'

 DECLARE @cols NVARCHAR(MAX) 
    SELECT @cols = COALESCE(@cols + ', ', '') + 'sum('+Name +') as ' + name
    FROM 
    (
        select name as name from tempdb.sys.columns 
        where object_id = object_id('tempdb..#t') and name!='day'
    )cols

then

DECLARE @sql NVARCHAR(MAX) = N'SELECT day,' 
    + @cols +
    ' FROM #t GROUP BY day';

and voila, the @sql variable has now a dynamic sql to what you want:

SELECT day,sum(code0) as code0, sum(code1) as code1, sum(code4) as code4 FROM #t GROUP BY day

now execute it

exec sp_executesql @sql

Please mark this as useful if it helped you. Thanks:)

Using group by but generate several columns

2 Answers2