How to flatten ranges of data using SQL-CodePudding

This is the original example sample data

This is what I would want to transform my data into

CodePudding user response：

Assuming you are using a version of SQL that supports recursion then this is a decent example of where it can be put to use!

CREATE TABLE example_data 
(id int, seat int, seat_num int, seat_last int, price float);

INSERT INTO example_data 
VALUES (1, 24, 3, 26, 15)
      ,(2, 36, 1, 36, 6)
      ,(3, 40, 1, 40, 6)
      ,(3, 42, 1, 42, 6)
      ,(4, 50, 2, 51, 11);

WITH recursive_query as (
    -- Set up the query so that you have something to iterate on 
    -- We'll use the seat_num, since we can count that down to 1
    SELECT id, seat, price/seat_num as price, seat_num
    FROM example_data

    UNION ALL

    -- In this section we call on the same CTE again. Therefore we set some 
    -- conditions (seat_num > 1 and reduce the seat_num by -1 each time it 
    -- is called) to stop an infinite loop.
    SELECT id, seat   1, price, seat_num - 1
    FROM recursive_query 
    WHERE seat_num > 1
)

SELECT id, seat, price
FROM recursive_query
ORDER BY id, seat

CodePudding user response：

This should help

DECLARE @sampleData TABLE (id int not null, seat int not null, seat_num int not null, seat_last int not null, price  int)
INSERT INTO @sampleData
VALUES 
(1,24,3,26, 15)  ,
(2,36,1,36,6) ,
(3,40,1,40, 6) ,
(3,42,1,42,6),
(4,50,2,51,11)

DECLARE @target_table TABLE(ID int, seat int, price  decimal(6,2))
DECLARE @Source_Table TABLE (id int not null, seat int not null, seat_num int not null, seat_last int not null, price  int not null, RowID INT)
INSERT INTO @Source_Table
SELECT *, ROW_NUMBER() OVER(ORDER BY id) as RowID FROM @sampleData

--SELECT * FROM @Source_Table

DECLARE @totalRecords INT = (SELECT COUNT(*) FROM @Source_Table)
DECLARE @seatNum INT = 0
DECLARE @currentSeat INT  = 0

DECLARE @targetPrice DECIMAL(10,2) = 0
DECLARE @currentPrice VARCHAR(10) = 0
DECLARE @currentID INT = 0

DECLARE @i INT = 1
DECLARE @j INT = 0

WHILE @i <= @totalRecords
BEGIN
    -- Find seat, price and number of seats for ID
    SELECT @currentID = id, @currentSeat = seat, @currentPrice = price, @seatNum = seat_num FROM @Source_Table WHERE RowID = @i
    SET @j = 1

    --Insert as many records as @seatNum
    WHILE @j <= @seatNum
    BEGIN
        SET @currentSeat = (CASE WHEN @j = 1 THEN @currentSeat ELSE @currentSeat   1 END)
        SET @targetPrice =  @currentPrice/CAST(@seatNum AS DECIMAL(10,2))
        SET @j =  @j   1

        INSERT INTO @target_table (ID, seat, price) VALUES (@currentID, @currentSeat, @targetPrice );
    END
    
    SET @i = @i   1
END

SELECT * FROM @target_table