TSQL PIVOT 列的一部分 [英] TSQL PIVOT Part of COLUMNS

问题描述

我有下表，但不确定是否可以旋转表中的某些列:

I have the following table but unsure of whether it is possible to pivot some of the columns from the table:

CREATE TABLE #test ( id int, Area varchar(10), [grouping] varchar(15), task_1 int, task_2 int)

INSERT INTO #test Values (10,'A','HighNeeds',1, 10)
INSERT INTO #test Values (10,'B','HighNeeds',1, 12)
INSERT INTO #test Values (12,'C','Non HighNeeds',2, 14)

select * from #test
-------------------------------------------------
id  Area    grouping        task_1  task_2
10  A       HighNeeds       1       10
10  B       HighNeeds       1       12
12  C       Non HighNeeds   2       14

What I'm trying to get is:
-------------------------------------------------
id  Area    Tasks      HighNeeds   Non HighNeeds   
10  A       task_1     1           NULL
10  A       task_2     10          NULL
10  B       task_1     1           NULL
10  B       task_2     12          NULL
12  C       task_1     NULL        2
12  C       takk_2     NULL        14

基本上我试图保留 ID 和 Area 列，但对分组数据和任务列进行透视.

Basically I'm trying to keep the ID and Area columns, but pivoting grouping data and tasks columns.

推荐答案

这很棘手 - 您需要将 Grouping 透视为列并取消透视 task_1 和 task_2 作为行值:

This is quite tricky - you need to pivot Grouping as columns and unpivot task_1 and task_2 as row values:

SELECT *
FROM
(
    SELECT 
        id, Area, grouping, tskCount, Task
    FROM 
        test
    UNPIVOT 
    (
       tskCount
       for Task in (task_1, task_2)
    ) unpvt
) X
PIVOT
(
 SUM(tskCount)
 for grouping in (HighNeeds, [Non HighNeeds])
)pvt;

在这里小提琴

这篇关于TSQL PIVOT 列的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！