Old School Transform: 1-X Pivot Table

ALTER PROC hGrid_MevDetails_Parent (
@costCenter CHAR (7)
, @FY CHAR (2)
)
AS
BEGIN

-- Joe Kelly
-- 2010-11-23 11:39:21.473
--
-- Parent proc that drives the hierarchal data grid in the
-- portal for labor something using Tmp_PreBuild_MEV_Details

-- EXEC hGrid_MevDetails_Parent '1601001', '10'

SET NOCOUNT ON

DECLARE @preXForm TABLE (
gDescription VARCHAR (50)
, gID INT
, Period INT -- CHAR (4)
, pMonth INT -- CHAR (2)
, sumBudget FLOAT
)

INSERT @preXForm (
gID
, Period
, pMonth
, sumBudget
)
SELECT tpmd.groupID, CAST(tpmd.Period AS INT), CAST(RIGHT(tpmd.Period, 2) AS INT), SUM(tpmd.Budget)
FROM Tmp_PreBuild_MEV_Details tpmd
WHERE CostCenter = '1707000' -- @costCenter CHAR (7)
AND LEFT(tpmd.Period, 2) = '10' -- @FY CHAR (2)
GROUP BY tpmd.GroupID, tpmd.Period

SELECT p.gID GroupID
, dg.Description
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 1
AND p.gID = gID
) AS p1
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 2
AND p.gID = gID
) AS p2
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 3
AND p.gID = gID
) AS p3
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 4
AND p.gID = gID
) AS p4
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 5
AND p.gID = gID
) AS p5
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 6
AND p.gID = gID
) AS p6
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 7
AND p.gID = gID
) AS p7
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 8
AND p.gID = gID
) AS p8
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 9
AND p.gID = gID
) AS p9
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 10
AND p.gID = gID
) AS p10
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 11
AND p.gID = gID
) AS p11
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 12
AND p.gID = gID
) AS p12
FROM @preXForm p
LEFT OUTER JOIN dat_groups dg
ON p.gID = dg.id
GROUP BY p.gID
, dg.Description
, dg.SortOrder
ORDER BY ISNULL(dg.SortOrder, 999)

END