SQL Server Pivot Table with multiple column aggregates

sql-server sql-server-2005 pivot unpivot

117,565

Solution 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 |

Solution 2

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.

Solution 3

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)

117,565

codingguy3000

I am a SQL Server developer

Updated on August 12, 2020

Comments

codingguy3000 over 3 years

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

Bogdan Sahlean about 11 years

The list of countries is static (only Australia and Austria) ?
codingguy3000 about 11 years

No but I don't want to make the question complicated. If I can get the answer for these two countries I can make the query dynamic.
Swapnil Gharat almost 3 years

Solution!!! use this stackoverflow.com/questions/15274305/…

Jacco about 11 years

"Lots of others are way better!" (LOTS OF OTHERS from Real Life) :-D
Bogdan Sahlean about 11 years

This solution adds an unnecessary level of complexity to a fairly simple query.
Jacco about 11 years

Did not see this at first, but have to admit you're right. +1 for you too.
Jacco about 11 years

Well, you have to admit your mind does strange things... But it does provide solutions.
Bogdan Sahlean about 11 years

While I like PIVOT, I don't think that it has some benefits (performance, readability) in this case.
Taryn about 11 years

@BogdanSahlean If they need a dynamic version then PIVOT will be best
kuklei over 9 years

@bluefeet why is it that when I swap charDate with country I get the desired columns but also the same number of rows instead of just two rows Austria, Australia. Here is what i did sqlfiddle.com/#!3/4bd75/397
Taryn over 9 years

@kuklei The subquery should only include the columns needed for the final display or the pivot - so remove the numericmonth -- sqlfiddle.com/#!3/4bd75/398