Sql -作为列的行

Question

我有以下格式的数据，大约8到9个部门，每个部门都有几个问题。

| Department | NoOfCases | Question | Rate |
+============+===========+==========+======+
| VC         | 4         | A        | 80   |
| VC         | 2         | B        | 90   |
| VC         | 1         | C        | 95   |
| ED         | 5         | A        | 85   |
| ED         | 1         | B        | 90   |
| ED         | 3         | C        | 95   |
| PH         | 3         | A        | 80   |

我想进入下面的格式，我希望每个部门的案例总数和每个问题的列和它的价值。

| Department | NoOfCases | A  | B  | C(actual questions as columns) |
+============+===========+====+====+================================+
| VC         | 7         | 80 | 90 | 95                             |
| ED         | 9         | 85 | 90 | 95                             |
| PH         | 3         | 80 |    |                                |

我们能做到这一点吗？

Answer 1

您可以使用带有GROUP BY的PIVOT来实现它

--create table variable to hold sample data
declare @tmp table( Department nvarchar(2),NoOfCases int, Question nvarchar(1), Rate int)

--populate sample data
insert into @tmp select 'VC', 4,'A', 80 
insert into @tmp select 'VC', 2,'B', 90 
insert into @tmp select 'VC', 1,'C', 95 
insert into @tmp select 'ED', 5,'A', 85 
insert into @tmp select 'ED', 1,'B', 90 
insert into @tmp select 'ED', 3,'C', 95 
insert into @tmp select 'PH', 3,'A', 80 

select * from @tmp

--pivot with group by
select Department,SUM(piv.NoOfCases) AS NoOfCases, 
    ISNULL(SUM(A),0) AS A, ISNULL(SUM(B),0) AS B, ISNULL(SUM(C),0) AS C
from 
( 
--select data
select  Department,NoOfCases  , Question ,RATE
from @tmp 
) src 
pivot 
( 
MAX(RATE) 
for Question in ([A], [B], [C]) 
) piv
GROUP BY Department

以下是命令的输出：

​

​