Is it possible to take a table that is structured in the following fashion:
ID Month Info1 Info2
1 1 A B
1 2 C D
1 3 E F
2 3 G H
2 4 I J
That ends up into a table like this:
ID JanInfo1 JanInfo2 FebInfo1 FebInfo2 MarInfo1 MarInfo2 AprInfo1 AprInfo2
1 A B C D E F NULL NULL
2 NULL NULL NULL NULL G H I J
I've looked into using pivots and couldn't get them to work.
I currently use CROSS APPLY table valued functions for each each month.
Is there a better way to do this?
Edit: Added existing query - tried to simplify for display:
-- Get the unique IDs
DECLARE @PersonIds TABLE
(
UploadID UNIQUEIDENTIFIER,
PersonId VARCHAR(200),
RecordYear INT
)
INSERT INTO @PersonIds
SELECT DISTINCT UploadID, PersonId, RecordYear
FROM [VERTICALTABLE]
WHERE UploadID = @PTPUploadID
AND RecordYear = @RecordYear
GROUP BY UploadID, PersonId, RecordYear
-- Flatten via functions
INSERT INTO [FLATTABLE](PersonID, JanuaryCoverage, FebruaryCoverage, MarchCoverage, AprilCoverage, MayCoverage, JuneCoverage,
JulyCoverage, AugustCoverage, SeptemberCoverage, OctoberCoverage, NovemberCoverage, DecemberCoverage)
SELECT PID.PersonID, M1.Covered, M2.Covered, M3.Covered, M4.Covered, M5.Covered, M6.Covered,
M7.Covered, M8.Covered, M9.Covered, M10.Covered, M11.Covered, M12.Covered
FROM @PersonIds AS PID
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 1, PID.PersonId) AS M1
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 2, PID.PersonId) AS M2
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 3, PID.PersonId) AS M3
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 4, PID.PersonId) AS M4
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 5, PID.PersonId) AS M5
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 6, PID.PersonId) AS M6
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 7, PID.PersonId) AS M7
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 8, PID.PersonId) AS M8
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 9, PID.PersonId) AS M9
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 10, PID.PersonId) AS M10
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 11, PID.PersonId) AS M11
OUTER APPLY GetMonthInfpo(@PTPUploadID, @RecordYear, 12, PID.PersonId) AS M12
WHERE UploadID = @PTPUploadID
AND RecordYear = @RecordYear
The functions look like
ALTER FUNCTION GetMonthInfpo(
(
@UploadID UNIQUEIDENTIFIER,
@Year INT,
@Month INT,
@PersonID VARCHAR(200)
)
RETURNS TABLE
AS
RETURN
(
SELECT COUNT(*) AS 'Covered'
FROM [VERTICALTABLE]
WHERE UploadID = @UploadID
AND RecordYear = @Year
AND RecordMonth = @Month
AND PersonId = @PersonID
)
You don't need multiple subqry. Answer is very easy - use set theory. From your first table ID/Month/Info1/Info2 do ID/Month + (1+2) /Info with easy union - for example:
select ID, cast(Month as varchar(10)) + cast('_1' as varchar(10)) ComposedMonth, Info1 Info
from tbl
union all
select ID, cast(Month as varchar(10)) + cast('_2' as varchar(10)), Info2
from tbl
Then use on this dataset (presented as view or temp table) pivot
clause.
select *
from vw_tbl t
pivot (max(Info) for ComposedMonth in ([1_1], [1_2]...)) p
-- or if you will cast month to text
-- pivot (max(Info) for ComposedMonth in ([Jan_1], [Jan_2]...)) p
Composing strings is the key for easy pivoting.