PIVOT / UNPIVOT

Question

I have two tables, one with status - this is a changing list - so a value could be added next week, e.g., 4 | Deferred:

ID | Status
1  | Open
2  | Closed
3  | Pending

The other with tasks:

ID | Name   | Status ID
1  | Task A | 1
2  | Task B | 1
3  | Task A | 2
4  | Task A | 3
5  | Task C | 2

And I want to output the sum of each task (type) in each of the potential status:

Task   | Open | Closed | Pending
Task A | 1    | 1      | 1
Task B | 1    | 0      | 0
Task C | 0    | 1      | 0

I believe a PIVOT can handle the dynamic rows to columns but haven't been able to translate the dynamic less than dot example to what I'm after.

Answer 1

@lojkyelo - this should give you the logic you need. Basically you will need a pivot with dynamic column extracted from the status table. @PivotColumn will then be passed using dynamic query.

See a mock up here http://rextester.com/FSN2383 and query below:

CREATE TABLE  #Status ([ID] int, [Status] varchar(max))

INSERT INTO #Status 
SELECT 1,'Open' UNION ALL
SELECT 2,'Closed' UNION ALL
SELECT 3,'Pending' UNION ALL
SELECT 4,'Deferred' 

CREATE TABLE  #Task ([ID] int, [Name] varchar(max), StatusID int)
INSERT INTO #Task 
SELECT 1, 'Task A', 1 UNION ALL
SELECT 2, 'Task B', 1 UNION ALL
SELECT 3, 'Task A', 2 UNION ALL
SELECT 4, 'Task A', 3 UNION ALL
SELECT 5, 'Task C', 2 UNION ALL
SELECT 6, 'Task C', 4 



DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += QUOTENAME(Status)+','
    FROM (SELECT p.Status FROM #Status p group by p.Status
    ) AS x;


DECLARE @Pivotcolumns NVARCHAR(MAX)=(select  left (@columns, Len ( @columns) - 1 ))

DECLARE @query NVARCHAR(MAX) = '
      SELECT *
      FROM
      (
            SELECT 
                s.Status,t.Name,StatusID=sum(t.StatusID)
            FROM #Status S
            LEFT JOIN #Task T ON 
                t.StatusID = s.id
            GROUP BY
            s.Status,t.Name

      ) x1
      PIVOT
      (
        COUNT(StatusID)
        for [Status] in ('+ @Pivotcolumns +')
      ) p'

EXEC(@query)

Answer 2

Try this Using Pivot

DECLARE @Table AS TABLE(ID INT, Status VARCHAR(10))
INSERT INTO @Table
SELECT 1,'Open'     UNION ALL
SELECT 2,'Closed'   UNION ALL
SELECT 3,'Pending'

DECLARE @Table2 AS TABLE(ID INT, Name  VARCHAR(10), StatusID VARCHAR(10))
INSERT INTO @Table2

SELECT 1,'Task A',1 UNION ALL
SELECT 2,'Task B',1 UNION ALL
SELECT 3,'Task A',2 UNION ALL
SELECT 4,'Task A',3 UNION ALL
SELECT 5,'Task C',2

;WITH CTE
AS
(
SELECT T1.Name, 
        T1.StatusID,
        t2.[Status] 
FROM @Table2 T1
INNER JOIN @Table T2
 ON t1.StatusID=T2.ID
)
SELECT Name,
       [Open],
       [Closed],
       [Pending]
FROM
(
SELECT * FROM CTE
)AS Src
PIVOT
(
COUNT(StatusID) FOR [Status] IN ([Open],[Closed],[Pending])
) AS PVT

Result

Name    Open Closed Pending
------------------------------
TaskA    1     1         1
TaskB    1     0         0
TaskC    0     1         0

Answer 3

To manage new statuses you can use dynamic TSQL:

create table #status([ID] int, [Status] varchar(max))

 insert into #status values
 (1  ,'Open')
,(2  ,'Closed')
,(3  ,'Pending')

create table #task([ID] int, [Name] varchar(max), StatusID int)

insert into #task values
 (1, 'Task A', 1)
,(2, 'Task B', 1)
,(3, 'Task A', 2)
,(4, 'Task A', 3)
,(5, 'Task C', 2)


declare @sql nvarchar(max)='select t.name as [Name]  ' 
select @sql = @sql + ', sum(case when s.Status = ''' + [Status] + ''' then 1 else 0 end) as [' + [Status] + ' ] '

from #status

select @sql = @sql + ' from #status s left join'
select @sql = @sql + '      #task t'
select @sql = @sql + '      on t.StatusID = s.id'
select @sql = @sql + ' group by t.name;'

execute(@sql)

Result with your data:

Adding 2 more statuses (Status4 and Status5):

create table #status([ID] int, [Status] varchar(max))

 insert into #status values
 (1  ,'Open')
,(2  ,'Closed')
,(3  ,'Pending')
,(4  ,'Status4')
,(5  ,'Status5')

create table #task([ID] int, [Name] varchar(max), StatusID int)

insert into #task values
 (1, 'Task A', 1)
,(2, 'Task B', 1)
,(3, 'Task A', 2)
,(4, 'Task A', 3)
,(5, 'Task C', 2)
,(6, 'Task D', 4)
,(7, 'Task D', 5)


declare @sql nvarchar(max)='select t.name as [Name]  ' 
select @sql = @sql + ', sum(case when s.Status = ''' + [Status] + ''' then 1 else 0 end) as [' + [Status] + ' ] '

from #status

select @sql = @sql + ' from #status s left join'
select @sql = @sql + '      #task t'
select @sql = @sql + '      on t.StatusID = s.id'
select @sql = @sql + ' group by t.name;'

execute(@sql)

Results:

Answer 4

Refer this query[][]

SELECT t2.name
        ,max(CASE 
                WHEN t2.statID = 1
                    THEN 1
                ELSE 0
                END) AS

    OPEN
        ,max(CASE 
                WHEN t2.statID = 2
                    THEN 1
                ELSE 0
                END) AS Closed
        ,max(CASE 
                WHEN t2.statID = 3
                    THEN 1
                ELSE 0
                END) AS pending
    FROM t2
    INNER JOIN t1 ON t1.INT = t2.statID
    GROUP BY t2.name

Answer 5

You can do conditional aggregation:

select t.name, 
       sum(case when s.Status = 'Open' then 1 else 0 end) as Open,
       sum(case when s.Status = 'Closed' then 1 else 0 end) as Closed,
       sum(case when s.Status = 'Pending' then 1 else 0 end) as Pending
from status s left join
     tasks t
     on t.StatusID = s.id
group by t.name;