SSRS combining multiple rows from multiple columns into one row

Question

We need to check requirements for students exams (e.g. extra time allocated (A), word processor (B), reader (C), and other). Currently the SQL pumps out the information like this:

Student | Exam Requirements
---------------------------
   1    |        A
   1    |        B
   2    |        A
   2    |        B
   2    |        C
   3    |        B
   4    |        B
   4    |        C
   5    |        A
   6    |        D
   7    |        E
   8    |        F

and I need to display it in SSRS like this:

Student | Requirement A | Requirement B | Requirement C | Other requirement

   1    |       Y       |       Y       |       -       |       -        
   2    |       Y       |       Y       |       Y       |       -        
   3    |       -       |       Y       |       -       |       -        
   4    |       -       |       Y       |       Y       |       -       
   5    |       Y       |       -       |       -       |       -       
   6    |       -       |       -       |       -       |       D       
   7    |       -       |       -       |       -       |       E       
   8    |       -       |       -       |       -       |       F        

Currently, as the row group is grouped on the Student, my table only shows a 'Y' in the column for Requirement A. If I group it on requirement then it creates as many rows as the student has requirements.

Thanks, Rob

Answer 1

Use conditional aggregation:

select 
    student,
    coalesce(max(case when exam_requirements = 'A' then 'Y' end), '-') requirement_a,
    coalesce(max(case when exam_requirements = 'B' then 'Y' end), '-') requirement_b,
    coalesce(max(case when exam_requirements = 'C' then 'Y' end), '-') requirement_c,
    coalesce(max(case when exam_requirements not in ('A', 'B', 'C') then exam_requirements end), '-') other_requirement
from mytable
group by student

Answer 2

I recreated the dataset with the following query

DECLARE @t TABLE(Student int, Requirement varchar(10))
INSERT INTO @t VALUES 
(1, 'A'),(1, 'B'),(2, 'A'),(2, 'B'),(2, 'C'),(3, 'B'),(4, 'B'),(4, 'C'),(5, 'A'),(6, 'D'),(7, 'E'),(8, 'F')

Then used this dataset in the report

SELECT 
    Student
    , CASE WHEN Requirement IN ('A','B','C') THEN Requirement ELSE 'Other' END AS ExamRequirement
    , CASE WHEN Requirement IN ('A','B','C') THEN 'Y' ELSE Requirement END AS reportvalue
    FROM @t

This gives the following output

I then added a Matrix to the report, set the row group by Student and the column group by ExamRequirement and the [data] to reportValue. The design looks like this

The final output looks like this