I need to know how to create a crosstab query

Question

I need help creating the below results. I thought of a sql pivot but I don't know how to use it. Looked at a few examples and cannot come up with a solution. Any other ideas on how to accomplish this is also welcome. Status columns must be dynamically generated.

Have three tables, assets, 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.) 

Desired results:

 AssetType     Total   Deployed   Inventory  Shipped     ... ----------------------------------------------------------- Desktop         100       75        20          5       ... Laptop           75       56        19          1       ... Server           60       50        10          0       ... 

Some Data:

 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 

Answer 1

This type of transformation is called a pivot. You did not specify what database you are using so I will provide a answers for SQL Server and MySQL.

---

SQL Server: If you are using SQL Server 2005+ you can implement the PIVOT function.

If you have a known number of values that you want to convert to columns then you can hard-code the query.

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; 

See SQL Fiddle with Demo.

But if you have an unknown number of status values, then you will need to use dynamic sql to generate the list of columns at run-time.

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) 

See SQL Fiddle with Demo

This can also be written using an aggregate function with a case expression:

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 

See SQL Fiddle with Demo

---

MySQL: This database does not have a pivot function so you will have to use the aggregate function and a CASE expression. It also does not have windowing functions, so you will have to alter the query slightly to the following:

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; 

See SQL Fiddle with Demo

Then if you need a dynamic solution in MySQL, you will have to use a prepared statement to generate the sql string to execute:

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; 

See SQL Fiddle with Demo.

The result is the same for all queries in both databases:

| TYPENAME | TOTAL | DEPLOYED | INVENTORY | SHIPPED | ----------------------------------------------------- |  Desktop |     2 |        1 |         1 |       0 | |   Laptop |     1 |        0 |         0 |       1 | |   Server |     1 |        1 |         0 |       0 |