Sql - Row as column

Question

I have data in below format, around 8 to 9 departments, for each department few questions.

| 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   |

I want into below format, I want total no of cases per department and every question as column and rate as its value.

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

Can we achieve this?

Answer 1

You can achieve it using a PIVOT with a GROUP BY:

--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

This is the output of the command: