Consider that I have a table which contains data in the following form:
Foo_FK MonthCode_FK Activity_FK SumResultsX SumResultsY
-----------------------------------------------------------
1 201312 0 10 2
1 201312 1 5 1
1 201401 0 15 3
1 201401 1 7 2
2 201312 0 9 3
2 201312 1 1 2
2 201401 0 6 2
2 201401 1 17 4
For my purposes, it is safe to assume that this table is an aggregation which would have been created by a GROUP BY on Foo_FK, MonthCode_FK, Activity_FK with SUM( ResultsA ), SUM( ResultsB ) to obtain the data, making Foo_FK, MonthCode_FK, Activity_FK unique per record.
If for some reason I found it preferable to PIVOT this table in a stored procedure to ease the amount of screwing around with SSRS I'd have to do ( and undoubtedly later maintain ), wishing to get the following format for consumption via a matrix tablix thingy:
Foo_FK 1312_0_X 1312_0_Y 1312_1_X 1312_1_Y 1401_0_X 1401_0_Y 1401_1_X 1401_1_Y
--------------------------------------------------------------------------------------
1 10 2 5 1 15 3 7 2
2 9 3 1 2 6 2 17 4
How would I go about doing this in a not-mental way? Please refer to this SQL Fiddle at proof I am likely trying to use a hammer to build a device that pushes in nails. Don't worry about a dynamic version as I'm sure I can figure that out once I'm guided through the static solution for this test case.
Right now, I've tried to create a Foo_FK, MonthCode_FK set via the following, which I then attempt to PIVOT ( see the Fiddle for the full mess ):
SELECT Foo_FK = ISNULL( a0.Foo_FK, a1.Foo_FK ),
MonthCode_FK = ISNULL( a0.MonthCode_FK, a1.MonthCode_FK ),
[0_X] = ISNULL( a0.SumResultX, 0 ),
[0_Y] = ISNULL( a0.SumResultY, 0 ),
[1_X] = ISNULL( a1.SumResultX, 0 ),
[1_Y] = ISNULL( a1.SumResultY, 0 )
FROM ( SELECT Foo_FK, MonthCode_FK, Activity_FK,
SumResultX, SumResultY
FROM dbo.t_FooActivityByMonth
WHERE Activity_FK = 0 ) a0
FULL OUTER JOIN (
SELECT Foo_FK, MonthCode_FK, Activity_FK,
SumResultX, SumResultY
FROM dbo.t_FooActivityByMonth
WHERE Activity_FK = 1 ) a1
ON a0.Foo_FK = a1.Foo_FK;
I have come across some excellent advice on this SO question, so I'm in the process of performing some form of UNPIVOT before I twist everything back out using PIVOT and MAX, but if there's a better way to do this, I'm all ears.
It seems that you should be able to do this by applying unpivot to your SumResultX and SumResultY columns first, then pivoting the data:
;with cte as
(
select Foo_FK,
col = cast(MonthCode_FK as varchar(6))+'_'
+cast(activity_fk as varchar(1))+'_'+sumresult,
value
from dbo.t_FooActivityByMonth
cross apply
(
values
('X', SumResultX),
('Y', SumResultY)
) c (sumresult, value)
)
select Foo_FK,
[201312_0_X], [201312_0_Y], [201312_1_X], [201312_1_Y],
[201401_0_X], [201401_0_Y], [201401_1_X], [201401_1_Y]
from cte
pivot
(
max(value)
for col in ([201312_0_X], [201312_0_Y], [201312_1_X], [201312_1_Y],
[201401_0_X], [201401_0_Y], [201401_1_X], [201401_1_Y])
) piv;