Making a row a column in SSRS

Question

I am trying to make what I think is a very simple report, however I can't seem to get the result I want and I don't even know if it is possible in SSRS.

It's a list of student applicants and the courses they have applied for. Where student apply for more than 1 course, for example 3 A Levels (the maximum they apply for is 5), I want the subjects to appear in different columns rather than a list in one column. A simple table shows the report like this: 3 Columns: student ref, applications status, course(s)

I want it to look like this: 7 columns: Student ref, Application status, course1, course2, course3 etc

I have tried to use a Matrix but the best I can get is where each subject has its own column, so I have a lot of blank text boxes and a very long report as we have 50 plus courses on offer.50+ Columns: Student ref, Application status, English, Maths, Biology, sport, French etc

Answer 1

Assuming this will work with your database, then this should be a pretty simple solution.

A matrix is the way to go I think as as you said, if you have lots of courses with at least one application then the report will be very wide and in manageable.

So, I recreated your sample and then queried it, adding a column that calculated a 'choice number'. this 'choice number' will only ever be between 1 and 5 (based on you statement that no student applies for more than 5 subjects)

Here's the sample data and simple query beneath

DECLARE @t TABLE([Student] varchar(10), [Status] varchar(10), [Subject] varchar(20))

INSERT INTO @t 
SELECT 'Stu001', 'OA', 'English' UNION
SELECT 'Stu001', 'OA', 'Maths' UNION
SELECT 'Stu002', 'OA', 'English' UNION
SELECT 'Stu002', 'OA', 'Biology' UNION
SELECT 'Stu002', 'OA', 'Sport' UNION
SELECT 'Stu003', 'OA', 'French' UNION
SELECT 'Stu003', 'OA', 'Chemistry' UNION
SELECT 'Stu003', 'OA', 'English' UNION
SELECT 'Stu004', 'OA', 'BTEC'

SELECT *
     , SubjectChoiceNumber = ROW_NUMBER() OVER(PARTITION BY Student, Status ORDER BY Subject)
    FROM @t

The SubjectChoiceNumber just assigns a sequential number. The sequence is sorted by the subject name.

This gives us the following output...

Now all you need to do is use a matrix control, set the column grouping to group by SubjectChoiceNumber and set the data cell expression to something like =FIRST(Fields!Subject.Value)

The report design looks like this

An the final output looks like this