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

Where do the (75, 56, 50) Deployed values come from? They don't appear in your data. — Eric S, Mar 30 '13 at 02:52
That doesn't make a lot of sense to me at the moment Do you have some examples of what is in your tables, a few actual rows... There must be some commonality between the tables as a point of reference to link them together... If you provide that detail I will have a go at it. — Matt Williams, Mar 30 '13 at 02:36
assets.assettype on assettypes.id, assets.assetstatus on assetstatus — Sam, Mar 30 '13 at 02:43
@EricS Those are the total counts for each type but the number don't match the sample data. — Taryn, Mar 30 '13 at 15:01

Taryn · Accepted Answer · 2013-03-30T03:17:42.907

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 |

SQL server with a known number worked beautifully. With an unknown number dynamic sql I get a couple of errors: Msg 1038, Level 15, State 4, Line 15 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. — Sam, Apr 01 '13 at 12:09
@Sam Can you edit the [SQL Fiddle](http://www.sqlfiddle.com/#!3/d7915/7) with the code you are trying to run? Place the code in the right panel and then execute sql. Then post the link in a comment here. — Taryn, Apr 01 '13 at 12:12
I create the tables, execute the code and all works fine. When I apply it to my db I get an error. Field names are correct. Don't know why it is not working. — Sam, Apr 01 '13 at 15:31
It is your code. Works fine, but not on my actual db which has exactly the same fields. — Sam, Apr 01 '13 at 15:46
OK, I found the issue, my assetstatus table had a record with a blank status, I changed the blank status to 'no status' and it all works now. Thanks a million. — Sam, Apr 01 '13 at 16:09

score 0 · Answer 2 · edited Oct 08 '18 at 19:21

Using a non pivot compliant DBMS (Absolute Database) I was more successful using this SQL cross-tab equivalent statement:

SELECT
  sub.TypeName
, SUM(sub.[Count]) AS "Total"
, SUM(CASE WHEN AssetStatus='1' THEN sub.[Count] ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN AssetStatus='2' THEN sub.[Count] ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN AssetStatus='3' THEN sub.[Count] ELSE 0 END) AS "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
 ) sub
GROUP BY sub.TypeName
;

As I realized this code (above) didn't work with MySQL I adapted my code as below executing equally well in MySQL as in my current Absolute Database. The reason is the specific NULL handling avoiding the pitfall of dBase, Paradox as well as Absolute Database generously accepting COUNT(NULL) = 0 not accepted in mainstream databases. So believing this will execute well in most databases (handling CASE ..) this is my adapted code:

SELECT
  sub.TypeName
, SUM(sub.AssetCase) AS "Total"
, SUM(CASE WHEN sub.StatusName = 'Deployed' THEN sub.AssetCase ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN sub.StatusName = 'Inventory' THEN sub.AssetCase ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN sub.StatusName = 'Shipped' THEN sub.AssetCase ELSE 0 END) AS "Shipped"
FROM
  (
   SELECT
     c.TypeName
   , c.StatusName
   , CASE WHEN a.AssetID IS NULL THEN 0 ELSE 1 END AS "AssetCase"
   FROM
     (
      SELECT
        t.ID AS tID
      , t.TypeName
      , s.ID AS sID
      , s.StatusName
      FROM
        AssetTypes t, AssetStatus s
     ) c
   LEFT JOIN Assets a
     ON a.AssetType = c.tID AND a.AssetStatus = c.sID
   ) sub
GROUP BY
  sub.TypeName
;

Best Regards Niels Knabe

I need to know how to create a crosstab query

2 Answers2

Linked