Formatting the output of SQL Server query

Question

I am new to SQL Server and SSRS...and am trying to get a matrix report formatted correctly.

My query is:

select e.name, ea.element,
 case when ea.element = 'projected_status' then 'Projected Status'
 when ea.element = 'projected_spstatus' then 'Projected Sp Status' 
 when ea.element = 'day1_status' then 'Day 1 Status'
 when ea.element = 'day1_spstatus' then 'Day 1 Sp Status'
 end as FIELD,
 el.value
from employeelocation el
join employee e on e.employeeid = ea.employeeid
join employeeattribute ea on ea.attributeid = el.attributeid
where ea.attributeid in (888,899);

and I am getting this information returned:

+--------------+-----------+-----------+-----+
| Barry Green | Projected | Status | 484 |
| Barry Green | Projected | Sp Status | 29 |
| Barry Green | Day 1 | Status | 589 |
| Barry Green | Day 1 | Sp Status | 2 |
| George Cart | Projected | Status | 324 |
| George Cart | Projected | Sp Status | 12 |
| George Cart | Day 1 | Status | 299 |
| George Cart | Day 1 | Sp Status | 0 |
+--------------+-----------+-----------+-----+

What I want to create is an output like this: Ideal Output

I can create the basic matrix report in SSRS, but I don't know how to format the groupings. I don't know if this is something that I need to do in my query or if there's a way to achieve this through format options in SSRS.

I just need to be pointed in the right direction. Any assistance is greatly appreciated.

Thank you!

Answer 1

I have put your results into one table and then adapted your query. I've taken out the joins and used GROUP BY and PIVOT you will need to put the joins back in to make it work for you.

select name,
 [Projected Status]=max([Projected Status]),
 [Projected Sp Status]=max([Projected Sp Status]),
 [Day 1 Status]=max([Day 1 Status]),
 [Day 1 Sp Status]=max([Day 1 Sp Status]),
 [%Diff]=cast(max([Day 1 Status])as float) / cast(max([Projected Status]) as float)*100
from 
(
 select name,
 element,
 value,
 row_number() over(partition by [element] order by [element]) rn
 from tableX
) t
pivot
(
 sum(value)
 for [element] in ([Projected Status], [Projected Sp Status],[Day 1 Status],[Day 1 Sp Status])
) piv
group by name

SQL Fiddle Demo