枢轴/ UNPIVOT

Question

我有两个表，一个具有状态--这是一个不断变化的列表--因此下周可以添加一个值，例如，4个延迟的值：

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

另一项任务是：

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

我希望输出每个任务(类型)在每个潜在状态中的总和：

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

我相信支点可以将动态行处理为列，但还无法将动态小于点的例子转换为我所追求的。

Answer 1

要管理新状态，可以使用动态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)

根据您的数据得出的结果：

​

​

添加另外两个状态(Status4和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)

结果：

​

​

Answer 2

用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

结果

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

Answer 3

@lojkyelo --这应该给您提供所需的逻辑。基本上，您需要从status表中提取动态列的枢轴。@PivotColumn将使用动态查询传递。

请参见此处的模拟，http://rextester.com/FSN2383和下面的查询：

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)