Tabela dinâmica do SQL Server com vários agregados de coluna

Eu tenho uma mesa:

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

A tabela tem esses registros:

 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 

É assim que um select * se parece:

 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 

Eu quero girar este conjunto de registros para que fique assim:

  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 

Este é o código de pivot que eu tenho até agora:

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

É isso que estou recebendo:

 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 

Eu posso agregar manualmente os registros em um loop de variável de tabela, no entanto, parece que o pivô pode ser capaz de fazer isso.

É possível obter o conjunto de registros desejado usando o pivô ou há outra ferramenta que não conheço?

obrigado

    Eu faria isso um pouco diferente aplicando as funções UNPIVOT e PIVOT para obter o resultado final. O unpivot obtém os valores das colunas totalcount e totalamount e os coloca em uma coluna com várias linhas. Você pode então girar sobre esses resultados:

     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 

    Veja SQL Fiddle with Demo .

    Se você tiver um número desconhecido de nomes de country , poderá usar o SQL dynamic:

     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) 

    Veja SQL Fiddle com Demo

    Ambos dão o resultado:

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

    Eu adicionei a consulta dinâmica / solução.

    Estático

     SELECT t.chardate, SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions", SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount", SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions", SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount" FROM mytransactions t GROUP BY t.chardate; 

    Nota:

    1) ORDER BY t.chardate não funcionará porque os valores da coluna chardate são char .

    2) Meu conselho é dividir chardate em duas colunas numericmonth e numericyear . Neste último caso, você poderia usar esta solução:

     SELECT t.numericyear, t.numericmonth, SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions", SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount", SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions", SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount" FROM mytransactions t GROUP BY t.numericyear, t.numericmonth ORDER BY BY t.numericyear, t.numericmonth; 

    Dinâmico

     DECLARE @Sql NVARCHAR(MAX)='SELECT t.chardate'; DECLARE @ColumnTemplate NVARCHAR(MAX)='SUM(CASE WHEN t.country=''{country}'' THEN t.totalcount ELSE 0 END) AS "{country} # of Transactions" ,SUM(CASE WHEN t.country=''{country}'' THEN t.totalamount ELSE 0 END) AS "{country} Total $ amount"' SELECT @Sql=@Sql+CHAR(13)+','+REPLACE(@ColumnTemplate, '{country}', REPLACE(c.name,'''','''''')e) FROM ( SELECT DISTINCT t.country AS name FROM mytransactions t ) c SELECT @Sql=@Sql+' FROM mytransactions t GROUP BY t.chardate;' PRINT @Sql; EXEC(@Sql); 

    Resultados:

     SELECT t.chardate ,SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions" ,SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount" ,SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions" ,SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount" FROM mytransactions t GROUP BY t.chardate; 

    Nota: A function REPLACE de SELECT @Sql=@Sql+CHAR(13)+ ... REPLACE(c.name,'''','''''')) é usada para evitar SQL injections .

    Eu usei seu próprio pivô como uma consulta aninhada e cheguei a este resultado:

     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 

    Aqui está o violino .

    A maneira menos complicada e mais direta de fazer isso é simplesmente agrupar sua consulta principal com o pivô em uma expressão de tabela comum e agrupar / agregar.

     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 

    O ISNULL é para impedir que um valor NULL anule a sum (porque NULL + qualquer valor = NULL )