sqlpivotpivot-table

I need to know how to create a crosstab query


I want to generate Status columns dynamically.

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

Solution

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