SQL generate value between rows

Question

I have 3 rows of data, and want to add value between rows. In excel I can use the excel formula and create manually, but I want to create it in SQL. The expected result is like the image :

In the picture, F3 is to get the average value between ID 1 and 2 and divide it to 10. After get the average value, use the first value in E3 and add value F3, and get 1.271111. E5 use E4 add value F3 and so on.

I want to add extra 10 row between different ID, and base on the difference value between ID, sum the previous value with the difference value. Isn't possible to do it in SQL statement?

1st of all you cannot have an `NULL` id! you can add data. possibly duplicate of http://stackoverflow.com/questions/9177177/how-to-insert-a-row-in-a-table-between-two-existing-rows-in-sql — Smit, Aug 29 '16 at 06:54
Kindly mention what exactly your excel sheet formula is doing. SQL Server wont support null / duplicate ids. If you want id column not to be an 'ID' / primary key and just a column, it could be possible. sorry but putting "-1" to it as question is not properly framed. — Surajit Biswas, Aug 29 '16 at 06:58
Maybe Arzozeus meant that SELECT results should return extra 10 rows between each ID. So it's not about adding to the original table, but about showing the expected results from the query (which can be then added to some other table) — Anton, Aug 29 '16 at 07:02
@Smit, sorry for making mistake, the id is generate in SQL, increment each row. — Arzozeus, Aug 29 '16 at 07:04
@SurajitBiswas , sorry for making mistake, the id is generate in SQL, increment each row. My excel formula in left, is to get the difference value between ID 1 and 2, and divide into length between the excel row, then get the average value. The formula in the right is the first value add the average, then after the value added, the third column will use the second column value add the average value and so on — Arzozeus, Aug 29 '16 at 07:04
@Anton, I will read from the original table, and after added the value and put it to another table for the next step — Arzozeus, Aug 29 '16 at 07:05
Arzozeus, I had to go. I'll review it tomorrow and give you an idea if it is not yet solved. At high level, you can use recursive CTE or prepare number table (from 1 to 10) or table function, and join to that table for every ID and calculate the intermediate values by multiplying the number by the increment... there are a lot of options. — Anton, Aug 29 '16 at 07:17
@Anton thanks for your help and sharing, I will try my own as well. Thank you — Arzozeus, Aug 29 '16 at 07:19

score 2 · Answer 1 · answered Aug 29 '16 at 08:09

I'm confused about your average.

SELECT (10 - 0.18) / 9 -- 1.091111 -- This is your average?
--
SELECT (10 - 0.18) / 10 -- 0.982000000
SELECT (32.11 - 10) / 10 -- 2.211000

You can as the below:

DECLARE @Tbl TABLE (Id INT, Value DECIMAL(7, 5))
INSERT INTO @Tbl        
VALUES  
(1, 0.18),
(2, 10),
(3, 32.11)

;WITH CTE
AS 
(
    SELECT 
        Id ,
        Value, 
        COALESCE((LEAD(Value) OVER (ORDER BY Id) * 1.0 - Value * 1.0) / 10, 0) AS AverageValue
    FROM @Tbl
)

--SELECT (10 - 0.18) / 10 -- 0.982000000
--SELECT (32.11 - 10) / 10 -- 2.211000

SELECT DISTINCT 
    A.Id,
    COALESCE((B.RowId * 1.0 * A.AverageValue) + A.Value,0) AS Result
FROM
    CTE A CROSS JOIN
    (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) B(RowId)

Note: This is for 2012+

score 1 · Answer 2 · answered Aug 29 '16 at 10:56

You need recursive CTE:

;WITH YourTable AS ( --simulation of your table
    SELECT * FROM (VALUES (1,0.18),(2,10),(3,32.11)) as t(Id,[Value])
), cte AS ( --Don't know what version of SQL Server you are using
    SELECT  a.ID,   --so I use OUTER APPLY to get next string value
            cast(a.[Value] as float) [Value],
            ISNULL(CAST((b.[Value]-a.[Value])/10 as float),0.00) as Seq
    FROM YourTable a
    OUTER APPLY (
        SELECT TOP 1 *
        FROM YourTable
        WHERE a.ID < ID
        ORDER BY ID ASC
        ) b
), final AS (
    SELECT  c.ID,
            c.[Value],
            c.Seq
    FROM cte c
    UNION ALL
    SELECT  CASE WHEN f.[Value]+f.Seq = c.[value] THEN c.id ELSE f.id END,
            f.[Value]+f.Seq,
            CASE WHEN ROUND(f.[Value]+f.Seq,2) = ROUND(c.[value],2) THEN c.Seq ELSE f.Seq END
    FROM final f
    INNER JOIN cte c
        ON c.ID = f.ID+1 and
             ROUND(f.[Value]+f.Seq,2) < ROUND(c.[value],2)
    WHERE f.seq > 0 
)

SELECT  ROW_NUMBER() OVER (ORDER BY [Value]) as ID,
        [Value],
        Seq
FROM final
ORDER BY id,[Value]
OPTION (MAXRECURSION 200)

Output:

ID  Value   Seq
1   0,18    0,982
2   1,162   0,982
3   2,144   0,982
4   3,126   0,982
5   4,108   0,982
6   5,09    0,982
7   6,072   0,982
8   7,054   0,982
9   8,036   0,982
10  9,018   0,982
11  10      2,211
12  12,211  2,211
13  14,422  2,211
14  16,633  2,211
15  18,844  2,211
16  21,055  2,211
17  23,266  2,211
18  25,477  2,211
19  27,688  2,211
20  29,899  2,211
21  32,11   0

SQL generate value between rows

2 Answers2