Projection/Replication in SQL Query?

Question

My SQL is a bit rusty -- is there a SQL way to project an input table that looks something like this:

Name                SlotValue              Slots
----                ---------              -----
ABC                 3                      1
ABC                 4                      2
ABC                 6                      5

Into a 'projected' result table that looks like this:

Name                SlotSum                 Slot
----                -------                 ----
ABC                 13                      1
ABC                 10                      2
ABC                 6                       3
ABC                 6                       4
ABC                 6                       5

In other words, the result set should contain a number of rows equal to MAX(Slots), enumerated (Slot) from 1 to MAX(Slots), and Sum for each of these 'slots' should reflect the sum of the SlotValues projected out to the 'Slots' position. for the pathological case:

Name               SlotValue                Slots
----               ---------                -----
ABC                4                        3

we should get:

Name               SlotSum                  Slot
----               -------                  ----
ABC                4                        1
ABC                4                        2
ABC                4                        3

The summation logic is pretty straightforward -- project each SlotValue out to the number of Slots:

SlotValue         SlotValue        SlotValue       Slot             Sum
---------         ---------        ---------       ----             ---
3                 4                6               1                13 (3+4+6)
0                 4                6               2                10 (0+4+6)
0                 0                6               3                6 (0+0+6)
0                 0                6               4                6 (0+0+6)
0                 0                6               5                6 (0+0+6)

UPDATE: In the end I used a variant of LOCALGHOST's approach in a stored proc. I was hoping there might be a way to do this without a loop.

Answer 1

I'm not sure you'll be able to do this in a view. You'd have to use a procedure. You could make ProjectedTable a temporary/variable table in the procedure as well. I'd be really interested to see how you'd get this into a view because you need to dynamically generate a range of numbers.

declare @maxSlot int 
set @maxSlot = select max(slots) from SlotTable

truncate ProjectedTable
while @i > 0 
    begin
    insert into ProjectedTable (
          SlotSum
          ,Slot
    ) values (
          (select sum(slotValue) from SlotTable where slots >= @maxSlot)
         ,@maxSlot
    )
    set @maxSlot = @maxSlot - 1 
end
select SlotSum, Slot from ProjectedTable

Answer 2

Here you go. This will do up to 100 slots in its current form. You can use your imagination to accommodate more.

DECLARE @SLOT TABLE
    (
    SlotName varchar(25) NOT NULL,
    SlotValue int NOT NULL,
    Slot int NOT NULL
    )

INSERT INTO @SLOT (SlotName, SlotValue, Slot)
    SELECT 'ABC', 3, 1
    UNION
    SELECT 'ABC', 4, 2
    UNION
    SELECT 'ABC', 6, 5

SELECT
    CASE
     WHEN SLOT.SlotName IS NOT NULL THEN SLOT.SlotName
     ELSE 
      COALESCE(
      (SELECT TOP 1 SL.SlotName FROM @SLOT AS SL WHERE SL.Slot < SLOT_PROJECT.Slot ORDER BY SL.Slot DESC),
      (SELECT TOP 1 SL.SlotName FROM @SLOT AS SL WHERE SL.Slot > SLOT_PROJECT.Slot ORDER BY SL.Slot ASC)
      )
    END AS SlotName,
    (
    SELECT
     SUM(SLOT10.SlotValue)
    FROM
     @SLOT AS SLOT10
    WHERE
     SLOT10.Slot >= SLOT_PROJECT.Slot
    ) AS SlotSum,
    SLOT_PROJECT.Slot
FROM
    (
    SELECT
     (TENS.Seq + ONES.Seq) AS Slot
    FROM
     (
     SELECT 1 AS Seq
     UNION ALL
     SELECT 2
     UNION ALL
     SELECT 3
     UNION ALL
     SELECT 4
     UNION ALL
     SELECT 5
     UNION ALL
     SELECT 6
     UNION ALL 
     SELECT 7
     UNION ALL
     SELECT 8
     UNION ALL
     SELECT 9
     ) AS ONES
     CROSS JOIN
     (
     SELECT 0 AS Seq
     UNION ALL
     SELECT 10
     UNION ALL
     SELECT 20
     UNION ALL
     SELECT 30
     UNION ALL
     SELECT 40
     UNION ALL
     SELECT 50
     UNION ALL
     SELECT 60
     UNION ALL 
     SELECT 70
     UNION ALL
     SELECT 80
     UNION ALL
     SELECT 90
     ) AS TENS
    WHERE
     (TENS.Seq + ONES.Seq) <= (SELECT MAX(Slot) FROM @SLOT)
    ) AS SLOT_PROJECT
    LEFT JOIN @SLOT AS SLOT ON
     SLOT.Slot = SLOT_PROJECT.Slot