multiples - Tabla dinámica de SQL Server con múltiples agregados de columnas

unpivot oracle

Question

Tengo una mesa:

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

La tabla tiene estos 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

Esto es lo que parece una selección *:

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

Quiero hacer pivotar este conjunto de registros para que se vea así:

 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 es el código de pivote que he encontrado hasta ahora:

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

Esto es lo que estoy recibiendo:

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

Puedo agregar manualmente los registros en un bucle de variable de tabla, sin embargo, parece que pivot podría ser capaz de hacer esto.

¿Es posible obtener el conjunto de registros que quiero usar pivot o hay otra herramienta que no conozco?

Gracias

Answer 1

Haría esto ligeramente diferente aplicando las funciones UNPIVOT y UNPIVOT para obtener el resultado final. Unpivot toma los valores de las columnas totalcount y totalamount y los coloca en una columna con varias filas. A continuación, puede pivotar en esos 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

Ver SQL Fiddle con Demo .

Si tiene un número desconocido de nombres de country , entonces puede usar SQL dinámico:

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)

Ver SQL Fiddle con Demo.

Ambos dan el 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 |

Answer 2

La forma menos complicada y directa de hacerlo es simplemente envolviendo su consulta principal con el pivote en una expresión de tabla común, luego agrupando / agregando.

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

El ISNULL es para evitar que un valor NULL ISNULL la suma (porque NULL + cualquier valor = NULL )

Answer 3

Usé su propio pivote como una consulta anidada y llegué 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

Aquí está el violín .

Answer 4

He añadido la consulta / solución dinámica.

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 no funcionará porque los valores de la columna chardate son caracteres.

2) Mi consejo es dividir chardate en dos columnas numericmonth y numericyear . En este último caso, podrías usar esta solución:

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

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: La función REPLACE de SELECT @Sql=@Sql+CHAR(13)+ ... REPLACE(c.name,'''''''','''''''''''')) se usa para evitar SQL injections .