SQL Server Pivot Table with multiple column aggregates

Question

I've got a table:

 create table mytransactions(country varchar(30), totalcount int, numericmonth int, chardate char(20), totalamount money) 

The table has these records:

insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 36, 7, 'Jul-12', 699.96) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 44, 8, 'Aug-12', 1368.71) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 52, 9, 'Sep-12', 1161.33) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 50, 10, 'Oct-12', 1099.84) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 38, 11, 'Nov-12', 1078.94) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 63, 12, 'Dec-12', 1668.23) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 11, 7, 'Jul-12', 257.82) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 5, 8, 'Aug-12', 126.55) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 7, 9, 'Sep-12', 92.11) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 12, 10, 'Oct-12', 103.56) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 21, 11, 'Nov-12', 377.68) Go insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 3, 12, 'Dec-12', 14.35) Go 

This is what a select * looks like:

Country         TotalCount numericmonth  chardate totalamount ---------       ---------- -----------   -------- ----------- Australia       36         7             Jul-12   699.96 Australia       44         8             Aug-12   1368.71 Australia       52         9             Sep-12   1161.33 Australia       50         10            Oct-12   1099.84 Australia       38         11            Nov-12   1078.94 Australia       63         12            Dec-12   1668.23 Austria         11         7             Jul-12   257.82 Austria          5         8             Aug-12   126.55 Austria          7         9             Sep-12   92.11 Austria         12         10            Oct-12   103.56 Austria         21         11            Nov-12   377.68 Austria          3         12            Dec-12   14.35 

I want to pivot this record set so it looks like this:

                   Australia          Australia        Austria              Austria                    # of Transactions  Total $ amount   # of Transactions    Total $ amount                    -----------------  --------------   -----------------    -------------- Jul-12             36                 699.96           11                   257.82 Aug-12             44                 1368.71          5                    126.55 Sep-12             52                 1161.33          7                    92.11 Oct-12             50                 1099.84          12                   103.56 Nov-12             38                 1078.94          21                   377.68 Dec-12             63                 1668.23           3                   14.35 

This is the pivot code I've come up with so far:

select * from  mytransactions pivot (sum (totalcount) for country in ([Australia], [Austria])) as pvt 

This is what I'm getting:

numericmonth     chardate     totalamount     Australia   Austria -----------      --------     ----------      ---------   ------- 7                Jul-12       257.82          NULL        11 7                Jul-12       699.96          36          NULL 8                Aug-12       126.55          NULL        5 8                Aug-12       1368.71         44          NULL 9                Sep-12       92.11           NULL        7 9                Sep-12       1161.33         52          NULL 10               Oct-12       103.56          NULL        12 10               Oct-12       1099.84         50          NULL 11               Nov-12       377.68          NULL        21 11               Nov-12       1078.94         38          NULL 12               Dec-12       14.35           NULL        3 12               Dec-12       1668.23         63          NULL 

I can manually aggregate the records in a table variable loop, however it seems that pivot might be able to do this.

Is is possible to get the record set I want using pivot or is there another tool that I'm not aware of?

Thanks

Answer 1

I would do this slightly different by applying both the UNPIVOT and the PIVOT functions to get the final result. The unpivot takes the values from both the totalcount and totalamount columns and places them into one column with multiple rows. You can then pivot on those results.:

select chardate,   Australia_totalcount as [Australia # of Transactions],    Australia_totalamount as [Australia Total $ Amount],   Austria_totalcount as [Austria # of Transactions],    Austria_totalamount as [Austria Total $ Amount] from (   select      numericmonth,      chardate,     country +'_'+col col,      value   from   (     select numericmonth,        country,        chardate,       cast(totalcount as numeric(10, 2)) totalcount,       cast(totalamount as numeric(10, 2)) totalamount     from mytransactions   ) src   unpivot   (     value     for col in (totalcount, totalamount)   ) unpiv ) s pivot (   sum(value)   for col in (Australia_totalcount, Australia_totalamount,               Austria_totalcount, Austria_totalamount) ) piv order by numericmonth 

See SQL Fiddle with Demo.

If you have an unknown number of country names, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),     @colsName AS NVARCHAR(MAX),     @query  AS NVARCHAR(MAX)  select @cols = STUFF((SELECT distinct ',' + QUOTENAME(country +'_'+c.col)                        from mytransactions                       cross apply                        (                         select 'TotalCount' col                         union all                         select 'TotalAmount'                       ) c             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')          ,1,1,'')  select @colsName      = STUFF((SELECT distinct ', ' + QUOTENAME(country +'_'+c.col)                 +' as ['                + country + case when c.col = 'TotalCount' then ' # of Transactions]' else 'Total $ Amount]' end              from mytransactions              cross apply               (                 select 'TotalCount' col                 union all                 select 'TotalAmount'              ) c             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')          ,1,1,'')  set @query    = 'SELECT chardate, ' + @colsName + '       from       (       select          numericmonth,          chardate,         country +''_''+col col,          value       from       (         select numericmonth,            country,            chardate,           cast(totalcount as numeric(10, 2)) totalcount,           cast(totalamount as numeric(10, 2)) totalamount         from mytransactions       ) src       unpivot       (         value         for col in (totalcount, totalamount)       ) unpiv      ) s      pivot       (        sum(value)        for col in (' + @cols + ')      ) p       order by numericmonth'  execute(@query) 

See SQL Fiddle with Demo

Both give the result:

|             CHARDATE | AUSTRALIA # OF TRANSACTIONS | AUSTRALIA TOTAL $ AMOUNT | AUSTRIA # OF TRANSACTIONS | AUSTRIA TOTAL $ AMOUNT | -------------------------------------------------------------------------------------------------------------------------------------- | Jul-12               |                          36 |                   699.96 |                        11 |                 257.82 | | Aug-12               |                          44 |                  1368.71 |                         5 |                 126.55 | | Sep-12               |                          52 |                  1161.33 |                         7 |                  92.11 | | Oct-12               |                          50 |                  1099.84 |                        12 |                 103.56 | | Nov-12               |                          38 |                  1078.94 |                        21 |                 377.68 | | Dec-12               |                          63 |                  1668.23 |                         3 |                  14.35 |