SQL Multiple count on same row with dynamic column

Question

I need to alter view that show user count(ScheduleID) by period on same row. Now the Period table content can grow and contain more than 3 periods.

The actual SQL is:

SELECT r.Code,
 SUM(CASE WHEN s.PeriodID=1 THEN 1 ELSE 0 END) AS PeriodID1,
 SUM(CASE WHEN s.PeriodID=2 THEN 1 ELSE 0 END) AS PeriodID2,
 SUM(CASE WHEN s.PeriodID=3 THEN 1 ELSE 0 END) AS PeriodID3,
 SUM(CASE WHEN s.PeriodID IN (1,2,3) THEN 1 ELSE 0 END) AS Total
 FROM Schedules s
 JOIN Periods p ON p.PeriodID = s.PeriodID
 JOIN Resources r ON r.ResourceID = s.ResourceID
 GROUP BY r.Code;

Example data: Table Schedules

ScheduleID(int) ResourceID(int) ResourceCode(varchar 4) PeriodID(int)
1               1               AA                      1
2               1               AA                      3
3               1               AA                      3
4               2               BB                      1
5               3               CC                      1
6               1               AA                      1
7               3               CC                      2
8               3               CC                      3
9               2               BB                      1
10              2               BB                      2
11              2               BB                      3
12              1               AA                      3

Table Periods

PeriodID(int) Code (varchar 4)
1             P1 
2             P2
3             P3
4             P4  
5             P5
6             P6
7             P7
8             P8

The result I need is:

ResourceCode PeriodID1 PeriodID2 PeriodID3 ... PeriodID8  TOTAL
AA           2         0         3             0          5
BB           2         1         1             0          4
CC           1         1         1             0          3

The Periods table content is now dynamic.

The database version is an Microsoft SQL 2008

I like to know if is possible to do that without create stored procedure...and doing this in one query like this:

SELECT *
FROM (
SELECT R.Code, P.PeriodID, COUNT(S.ScheduleID) AS RPCount
FROM Schedules S INNER JOIN Periods P ON S.PeriodID = P.PeriodID
JOIN Resources R ON S.ResourceID = R.ResourceID
WHERE S.ResourceID is not null
GROUP BY R.Code, P.PeriodID
) as data
PIVOT
(
    SUM(RPCount)
    --FOR PeriodID IN ([1],[2],[3])
    FOR PeriodID IN (SELECT PeriodID From Periods)
)AS pvt
ORDER BY Code

Answer 1

Use PIVOT

try this

SELECT *
    FROM (
        SELECT 
           S.ResourceCode ,
           P.PeriodID  AS period,
          COUNT(*) AS PCount
        FROM Schedules S INNER JOIN Periods P ON S.PeriodID =P.PeriodID 
        GROUP BY S.ResourceCode ,P.PeriodID
    ) as s
    PIVOT
    (

        PCount,
        FOR [period] IN (SELECT DISTINCT PeriodID From Periods)
    )AS pivot

Answer 2

Since you are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('PeriodId'+cast(periodid as varchar(10))) 
                    from Periods
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT resourcecode, ' + @cols + ' , Total
            from 
            (
               select s.resourcecode, 
                 ''PeriodId''+cast(p.periodid as varchar(10)) period,
                count(*) over(partition by s.resourcecode) Total
               from periods p
               left join schedules s
                 on p.periodid = s.periodid
            ) x
            pivot 
            (
                count(period)
                for period in (' + @cols + ')
            ) p 
            where resourcecode is not null
            order by resourcecode'

execute(@query)

See SQL Fiddle with Demo. This gives a result:

| RESOURCECODE | PERIODID1 | PERIODID2 | PERIODID3 | PERIODID4 | PERIODID5 | PERIODID6 | PERIODID7 | PERIODID8 | TOTAL |
------------------------------------------------------------------------------------------------------------------------
|           AA |         2 |         0 |         3 |         0 |         0 |         0 |         0 |         0 |     5 |
|           BB |         2 |         1 |         1 |         0 |         0 |         0 |         0 |         0 |     4 |
|           CC |         1 |         1 |         1 |         0 |         0 |         0 |         0 |         0 |     3 |

Based on your previous question that was tagged with MySQL, I am assuming you are using MySQL as the database. If so, then you do not have a PIVOT function so you will have to use an aggregate function with a CASE expression to transform the rows of data into columns.

If your column values are known, then you can hard-code the query:

select resourcecode,
  sum(case when period = 'PeriodId1' then 1 else 0 end) PeriodId1,
  sum(case when period = 'PeriodId2' then 1 else 0 end) PeriodId2,
  sum(case when period = 'PeriodId3' then 1 else 0 end) PeriodId3,
  sum(case when period = 'PeriodId4' then 1 else 0 end) PeriodId4,
  sum(case when period = 'PeriodId5' then 1 else 0 end) PeriodId5,
  sum(case when period = 'PeriodId6' then 1 else 0 end) PeriodId6,
  sum(case when period = 'PeriodId7' then 1 else 0 end) PeriodId7,
  sum(case when period = 'PeriodId8' then 1 else 0 end) PeriodId8,
  count(*) Total
from
(
  select concat('PeriodId', p.periodid) Period,
    s.resourcecode
  from periods p
  left join schedules s
    on p.periodid = s.periodid
) d
where resourcecode is not null
group by resourcecode;

See SQL Fiddle with Demo. But if the values will be unknown or dynamic then you will need to use a prepared statement to generate a sql string to execute:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN period = ''',
      concat('PeriodId', periodid),
      ''' THEN 1 else 0 END) AS `',
      concat('PeriodId', periodid), '`'
    )
  ) INTO @sql
FROM periods;

SET @sql 
  = CONCAT('SELECT resourcecode, ', @sql, ' , count(*) Total
            from
            (
              select concat(''PeriodId'', p.periodid) Period,
                s.resourcecode
              from periods p
              left join schedules s
                on p.periodid = s.periodid
            ) d
            where resourcecode is not null
            group by resourcecode');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo.

Answer 3

Please try below code for MS Sql server:

DECLARE @column VARCHAR(MAX), @SumQuery VARCHAR(MAX)

SELECT 
    @column = COALESCE(@column + '], [', '')+ CAST(PeriodID as nvarchar(10)),
    @SumQuery = COALESCE(@SumQuery + ']+[', '')+ CAST(PeriodID as nvarchar(10))
FROM 
    Periods 
GROUP BY PeriodID

EXEC ('select *, ['+@SumQuery+'] as [Total] From
(
    select * From Schedules
)up
pivot (count(ScheduleID) for PeriodID in (['+@column+'])) as pvt')