是否可以采用以下方式构建的表格:
ID Month Info1 Info2
1 1 A B
1 2 C D
1 3 E F
2 3 G H
2 4 I J
最终形成这样的表格:
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
我研究过使用枢轴但无法让它们工作。
我目前每个月都使用 CROSS APPLY 表值函数。
有一个更好的方法吗?
编辑:添加了现有查询 - 尝试简化显示:
-- 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
函数看起来像
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
)