SQL Refactor messes up PIVOT columns
davecorun
Posts: 10
After a Refactor, I ended up witht he following:
FROM CTE_ToPivot PIVOT ( MAX(TextValue) FOR Section IN ( [Target], [Baseline], [Strategy], [Quarter 1 Milestone], [Quarter 2 Milestone], [Quarter 3 Milestone], [Quarter 4 Milestone], [Quarter 1 Actual], [Quarter 2 Actual], [Quarter 3 Actual
], [Quarter 4 Actual], [Quarter 1 Course Correction], [Quarter 2 Course Correction], [Quarter 3 Course Correction], [Quarter 4 Course Correction], [Actual Annual], [Annual Met], [Quarter 1 Additional Milestone], [Quarter 2 Additional Milestone], [Quarter
3 Additional Milestone], [Quarter 4 Additional Milestone], [Quarter 1 Additional Milestone Name], [Quarter 2 Additional Milestone Name], [Quarter 3 Additional Milestone Name], [Quarter 4 Additional Milestone Name], [Quarter 1 Additional Milestone Actual], [Quarter 2 Additional Milestone Actual], [Quarter 3 Additional Milestone Actual], [Quarter 4 Additional Milestone Actual], [Quarter 1 Additional Milestone Course Correction], [Quarter 2 Additional Milestone Course Correction], [Quarter 3 Additional Miles
tone Course Correction], [Quarter 4 Additional Milestone Course Correction], [Quarter 1 Second Milestone], [Quarter 2 Second Milestone], [Quarter 3 Second Milestone], [Quarter 4 Second Milestone], [Quarter 1 Second Milestone Name], [Quarter 2 Second Miles
tone Name], [Quarter 3 Second Milestone Name], [Quarter 4 Second Milestone Name], [Quarter 1 Second Milestone Actual], [Quarter 2 Second Milestone Actual], [Quarter 3 Second Milestone Actual], [Quarter 4 Second Milestone Actual], [Quarter 1 Second Milesto
ne Course Correction], [Quarter 2 Second Milestone Course Correction], [Quarter 3 Second Milestone Course Correction], [Quarter 4 Second Milestone Course Correction] ) ) AS P
WHERE ( COALESCE(P.[Target], P.[Baseline], P.[Strategy],
P.[Quarter 1 Milestone],
It put newline characters in the wrong places, and completely messed up my stored procedure. I'll fix it by hand, but this was working in the previous version?
// Dave
FROM CTE_ToPivot PIVOT ( MAX(TextValue) FOR Section IN ( [Target], [Baseline], [Strategy], [Quarter 1 Milestone], [Quarter 2 Milestone], [Quarter 3 Milestone], [Quarter 4 Milestone], [Quarter 1 Actual], [Quarter 2 Actual], [Quarter 3 Actual
], [Quarter 4 Actual], [Quarter 1 Course Correction], [Quarter 2 Course Correction], [Quarter 3 Course Correction], [Quarter 4 Course Correction], [Actual Annual], [Annual Met], [Quarter 1 Additional Milestone], [Quarter 2 Additional Milestone], [Quarter
3 Additional Milestone], [Quarter 4 Additional Milestone], [Quarter 1 Additional Milestone Name], [Quarter 2 Additional Milestone Name], [Quarter 3 Additional Milestone Name], [Quarter 4 Additional Milestone Name], [Quarter 1 Additional Milestone Actual], [Quarter 2 Additional Milestone Actual], [Quarter 3 Additional Milestone Actual], [Quarter 4 Additional Milestone Actual], [Quarter 1 Additional Milestone Course Correction], [Quarter 2 Additional Milestone Course Correction], [Quarter 3 Additional Miles
tone Course Correction], [Quarter 4 Additional Milestone Course Correction], [Quarter 1 Second Milestone], [Quarter 2 Second Milestone], [Quarter 3 Second Milestone], [Quarter 4 Second Milestone], [Quarter 1 Second Milestone Name], [Quarter 2 Second Miles
tone Name], [Quarter 3 Second Milestone Name], [Quarter 4 Second Milestone Name], [Quarter 1 Second Milestone Actual], [Quarter 2 Second Milestone Actual], [Quarter 3 Second Milestone Actual], [Quarter 4 Second Milestone Actual], [Quarter 1 Second Milesto
ne Course Correction], [Quarter 2 Second Milestone Course Correction], [Quarter 3 Second Milestone Course Correction], [Quarter 4 Second Milestone Course Correction] ) ) AS P
WHERE ( COALESCE(P.[Target], P.[Baseline], P.[Strategy],
P.[Quarter 1 Milestone],
It put newline characters in the wrong places, and completely messed up my stored procedure. I'll fix it by hand, but this was working in the previous version?
// Dave
