CODEWITHSUNDEEP
sqlsql-server-2008pivot
kevro
kevro

Reputation: 273

Summarize output from a select

I have a SELECT statement that gathers data from several JOINS that produces this:

Site    Description Status
  1     Project 1   Not Started
  1     Project 5   In Progress
  1     Project 2   Testing
  1     Project 12  Complete
  1     Project 2   Not Started
  2     Project 13  Not Started
  2     Project 20  Testing
  2     Project 21  In Progress
  2     Project 22  In Progress
  2     Project 23  Testing
  3     Project 24  Not Started
  3     Project 25  Not Started
  3     Project 26  Testing
  3     Project 29  Complete

I want to summarize the status for each site to produce:

Site    Not Started In Progress Testing Complete
   1        2              1       1       1
   2        1              2       2       0
   3        2              0       1       1

I should be able to use the "WITH" clause to process the output. Currently, it would look like this:

WITH SiteData AS (SELECT data from multiple JOINS...)

SELECT Site, Description, Status FROM SiteData

So rather than just showing the columns of data, I want to transpose it into a count of projects in each of the various Statuses by Site.

Does that make sense?

Upvotes: 1

Views: 97

Answers (1)

Taryn
Taryn

Reputation: 247720

This type of data transformation is known as a pivot. There are several ways that you can convert the row data into columns.

You can use an aggregate function with a CASE:

select site,
  sum(case when Status = 'Not Started' then 1 else 0 end) [Not Started],
  sum(case when Status = 'In Progress' then 1 else 0 end) [In Progress],
  sum(case when Status = 'Testing' then 1 else 0 end) Testing,
  sum(case when Status = 'Complete' then 1 else 0 end) Complete
from sitedata
group by site

See SQL Fiddle with Demo.

You will replace the sitedata with your existing query.

Or you can use the PIVOT function:

select site,
  [Not Started], 
  [In Progress], 
  Testing, 
  Complete
from
(
  select [Site], Status
  from sitedata
) src
pivot
(
  count(Status)
  for Status in ([Not Started], [In Progress], Testing, Complete)
) piv

See SQL Fiddle with Demo.

If you have an unknown number of Status values, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Status]) 
                    from SiteData
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT site, ' + @cols + ' from 
             (
                select [Site], Status
                from sitedata
            ) x
            pivot 
            (
                count(Status)
                for Status in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo.

The result for each query is the same:

| SITE | NOT STARTED | IN PROGRESS | TESTING | COMPLETE |
---------------------------------------------------------
|    1 |           2 |           1 |       1 |        1 |
|    2 |           1 |           2 |       2 |        0 |
|    3 |           2 |           0 |       1 |        1 |

Edit #1, if you want a Total row, then you can use:

select 
  case when site is not null then cast(site as varchar(10)) else 'Total' end Site,
  sum([Not Started]) [Not Started], 
  Sum([In Progress])[In Progress], 
  Sum(Testing) Testing, 
  sum(Complete) Complete,
  Sum([Not Started]+[In Progress]+ Testing+ Complete) Total
from
(
  select [Site], Status
  from sitedata
) src
pivot
(
  count(Status)
  for Status in ([Not Started], [In Progress], Testing, Complete)
) piv
group by rollup (site)

See SQL Fiddle with Demo

Upvotes: 4

Related Questions