Preciso de saber como criar uma consulta crosstab
tem três quadros, activos, assettypes, assetstatus
Table: assets assetid int assettag varchar(25) assettype int assetstatus int Table: assettypes id int typename varchar(20) (ex: Desktop, Laptop, Server, etc.) Table: assetstatus id int statusname varchar(20) (ex: Deployed, Inventory, Shipped, etc.)
resultados desejados:
AssetType Total Deployed Inventory Shipped ... ----------------------------------------------------------- Desktop 100 75 20 5 ... Laptop 75 56 19 1 ... Server 60 50 10 0 ...
Alguns Dados:
assets table: 1,hol1234,1,1 2,hol1233,1,2 3,hol3421,2,3 4,svr1234,3,1 assettypes table: 1,Desktop 2,Laptop 3,Server assetstatus table: 1,Deployed 2,Inventory 3,Shipped
2 answers
Este tipo de transformação é chamado de pivô. Você não especificou que banco de dados você está usando então eu vou fornecer uma resposta para o servidor SQL e MySQL.
SQL Server: Se estiver a usar o SQL Server 2005 + pode implementar a função PIVOT.
Se você tem um número conhecido de valores que deseja converter para colunas, então você pode programar a consulta.
select typename, total, Deployed, Inventory, shipped
from
(
select count(*) over(partition by t.typename) total,
s.statusname,
t.typename
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
pivot
(
count(statusname)
for statusname in (Deployed, Inventory, shipped)
) piv;
Ver Violino SQL com demonstração .
Mas se tiver um desconhecido número de valores status, então terá de usar o SQL dinâmico para gerar a lista de colunas no tempo de execução.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(statusname)
from assetstatus
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT typename, total,' + @cols + ' from
(
select count(*) over(partition by t.typename) total,
s.statusname,
t.typename
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) x
pivot
(
count(statusname)
for statusname in (' + @cols + ')
) p '
execute(@query)
Ver Violino SQL com demonstração
Isto também pode ser escrito usando uma função agregada com uma expressão de Caso:
select typename,
total,
sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
select count(*) over(partition by t.typename) total,
s.statusname,
t.typename
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
group by typename, total
Ver Violino SQL com demonstração
MySQL: Esta base de dados não tem uma função pivot por isso terá de usar a função agregada e uma expressãoCASE. Também não tem funções de janelas, por isso terá de alterar ligeiramente a consulta para o seguinte:
select typename,
total,
sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
select t.typename,
(select count(*)
from assets a1
where a1.assettype = t.id
group by a1.assettype) total,
s.statusname
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
group by typename, total;
Ver Violino SQL com demonstração
Então, se precisar de uma solução dinâmica no MySQL, terá de usar uma declaração preparada para gerar o texto sql a executar:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN statusname = ''',
statusname,
''' THEN 1 else 0 END) AS `',
statusname, '`'
)
) INTO @sql
FROM assetstatus;
SET @sql
= CONCAT('SELECT typename,
total, ', @sql, '
from
(
select t.typename,
(select count(*)
from assets a1
where a1.assettype = t.id
group by a1.assettype) total,
s.statusname
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
group by typename, total');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Ver Violino SQL com demonstração .
O resultado é o mesmo para todas as consultas em ambas as bases de Dados:
| TYPENAME | TOTAL | DEPLOYED | INVENTORY | SHIPPED |
-----------------------------------------------------
| Desktop | 2 | 1 | 1 | 0 |
| Laptop | 1 | 0 | 0 | 1 |
| Server | 1 | 1 | 0 | 0 |
Usando um DBMS não compatível com pivot (base de dados Absolute) I foi mais bem sucedido usando esta declaração equivalente SQL Cross-tab:
SELECT
TypeName
, SUM([Count]) AS "Total"
, SUM(CASE WHEN AssetStatus='1' THEN [Count] ELSE 0 END) Deployed
, SUM(CASE WHEN AssetStatus='2' THEN [Count] ELSE 0 END) Inventory
, SUM(CASE WHEN AssetStatus='3' THEN [Count] ELSE 0 END) Shipped
FROM
(
SELECT
t.TypeName, AssetStatus
, COUNT(AssetID) AS "Count"
FROM
Assets
JOIN AssetTypes t ON t.ID = AssetType
JOIN AssetStatus s ON s.ID = AssetStatus
GROUP BY t.TypeName, AssetStatus, s.StatusName
)
GROUP BY TypeName
;