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

User · Answer

I used your own pivot as a nested query and came to this result:

SELECT
  [sub].[chardate],
  SUM(ISNULL([Australia], 0)) AS [Transactions Australia],
  SUM(CASE WHEN [Australia] IS NOT NULL THEN [TotalAmount] ELSE 0 END) AS [Amount Australia],
  SUM(ISNULL([Austria], 0)) AS [Transactions Austria],
  SUM(CASE WHEN [Austria] IS NOT NULL THEN [TotalAmount] ELSE 0 END) AS [Amount Austria]
FROM
(
  select * 
  from  mytransactions
  pivot (sum (totalcount) for country in ([Australia], [Austria])) as pvt
) AS [sub]
GROUP BY
  [sub].[chardate],
  [sub].[numericmonth]
ORDER BY 
  [sub].[numericmonth] ASC

Here is the Fiddle.

User · Answer

The least complicated  most straight-forward way of doing this is by simply wrapping your main query with the pivot in a common table expression  then grouping aggregating   WITH PivotCTE AS       select   from  mytransactions     pivot  sum  totalcount  for country in   Australia    Austria    as pvt   SELECT     numericmonth      chardate      SUM totalamount  AS totalamount      SUM ISNULL Australia  0   AS Australia      SUM ISNULL Austria  0   Austria FROM PivotCTE GROUP BY numericmonth  chardate   The ISNULL is to stop a NULL value from nullifying the sum  because NULL   any value   NULL

User · Answer

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

[sql-server] SQL Server Pivot Table with multiple column aggregates

Examples related to sql-server

Examples related to sql-server-2005

Examples related to pivot

Examples related to unpivot