Sql-server – How to find Linear interpolation without using pivot and unpivot in SQL Server 2008

Sample data

CREATE TABLE #Data
(
    id integer NOT NULL PRIMARY KEY,
    x integer NOT NULL,
    val float NOT NULL
);

INSERT #Data
    (id, x, val)
VALUES
    (1, 1, 8.18),
    (2, 3, 8.35),
    (3, 5, 8.51),
    (4, 7, 8.52),
    (5, 10, 8.54),
    (6, 15, 8.57),
    (7, 1, 8.35),
    (8, 3, 8.60),
    (9, 5, 8.70),
    (10, 7, 8.75),
    (11, 10, 8.76),
    (12, 15, 8.82),
    (13, 1, 8.55),
    (14, 3, 8.80),
    (15, 5, 8.91),
    (16, 7, 8.95),
    (17, 10, 8.96),
    (18, 15, 9.04),
    (19, 1, 8.75),
    (20, 3, 9.04),
    (21, 5, 9.15),
    (22, 7, 9.21),
    (23, 10, 9.24),
    (24, 15, 9.27);

Pivot

The first step is to pivot the data we have:

DECLARE @Pivoted table
(
    x integer NOT NULL PRIMARY KEY,
    val1 float NULL,
    val2 float NULL,
    val3 float NULL,
    val4 float NULL
);

-- Pivot the data we have
INSERT @Pivoted
    (x, val1, val2, val3, val4)
SELECT
    N.x,
    val1 = MAX(CASE WHEN N.rn = 1 THEN N.val ELSE 0 END),
    val2 = MAX(CASE WHEN N.rn = 2 THEN N.val ELSE 0 END),
    val3 = MAX(CASE WHEN N.rn = 3 THEN N.val ELSE 0 END),
    val4 = MAX(CASE WHEN N.rn = 4 THEN N.val ELSE 0 END)
FROM
(
    SELECT
        D.x,
        D.val,
        rn = ROW_NUMBER() OVER (
            PARTITION BY D.x 
            ORDER BY D.id)
    FROM #Data AS D
) AS N
GROUP BY N.x;

Next we need to know the range of values in the table so we can fill in the blanks:

DECLARE 
    @Min integer,
    @Max integer;

-- Range of values
SELECT
    @Min = PMin.x,
    @Max = PMax.x
FROM (SELECT TOP (1) P.x FROM @Pivoted AS P ORDER BY P.x ASC) AS PMin
CROSS JOIN (SELECT TOP (1) P.x FROM @Pivoted AS P ORDER BY P.x DESC) AS PMax;

Now we can find the missing rows, and the real rows on either side (for interpolation):

DECLARE @Missing table
(
    x integer NOT NULL PRIMARY KEY,
    prev_row integer NULL,
    next_row integer NULL
);

-- Find missing rows
INSERT @Missing
    (x, prev_row, next_row)
SELECT
    Missing.number,
    PrevRow.x,
    NextRow.x
FROM 
(
    -- All rows that ought to be present
    SELECT SV.number
    FROM master.dbo.spt_values AS SV
    WHERE
        SV.[type] = N'P'
        AND SV.number BETWEEN @Min AND @Max
    EXCEPT
    -- Those that are present
    SELECT P.x 
    FROM @Pivoted AS P
) AS Missing
CROSS APPLY
(
    -- Find the previous real row
    SELECT TOP (1) P.x
    FROM @Pivoted AS P
    WHERE P.x < Missing.number
    ORDER BY P.x DESC
) AS PrevRow
CROSS APPLY 
(
    -- Find the next real row
    SELECT TOP (1) P.x
    FROM @Pivoted AS P
    WHERE P.x > Missing.number
    ORDER BY P.x ASC
) AS NextRow;

Finally, we compute interpolated values for the missing rows, and return the results:

-- Results
SELECT
    Result.x,
    val1 = ROUND(Result.val1, 3),
    val2 = ROUND(Result.val2, 3),
    val3 = ROUND(Result.val3, 3),
    val4 = ROUND(Result.val4, 3)
FROM 
(
    -- Rows that exist
    SELECT
        P.x,
        P.val1,
        P.val2,
        P.val3,
        P.val4
    FROM @Pivoted AS P

    -- Plus
    UNION ALL

    -- Interpolated rows
    SELECT
        M.x,
        val1 = PrevRow.val1 + (Scale.factor * (NextRow.val1 - PrevRow.val1)),
        val2 = PrevRow.val2 + (Scale.factor * (NextRow.val2 - PrevRow.val2)),
        val3 = PrevRow.val3 + (Scale.factor * (NextRow.val3 - PrevRow.val3)),
        val4 = PrevRow.val4 + (Scale.factor * (NextRow.val4 - PrevRow.val4))
    FROM @Missing AS M
    JOIN @Pivoted AS PrevRow
        ON PrevRow.x = M.prev_row
    JOIN @Pivoted AS NextRow
        ON NextRow.x = M.next_row
    CROSS APPLY 
    (
        VALUES(CONVERT(float, M.x - M.prev_row) / CONVERT(float, M.next_row - M.prev_row))
    ) AS Scale (factor)
) AS Result
ORDER BY
    Result.x ASC;

Results

╔════╦═══════╦═══════╦═══════╦═══════╗
║ x  ║ val1  ║ val2  ║ val3  ║ val4  ║
╠════╬═══════╬═══════╬═══════╬═══════╣
║  1 ║ 8.18  ║ 8.35  ║ 8.55  ║ 8.75  ║
║  2 ║ 8.265 ║ 8.475 ║ 8.675 ║ 8.895 ║
║  3 ║ 8.35  ║ 8.6   ║ 8.8   ║ 9.04  ║
║  4 ║ 8.43  ║ 8.65  ║ 8.855 ║ 9.095 ║
║  5 ║ 8.51  ║ 8.7   ║ 8.91  ║ 9.15  ║
║  6 ║ 8.515 ║ 8.725 ║ 8.93  ║ 9.18  ║
║  7 ║ 8.52  ║ 8.75  ║ 8.95  ║ 9.21  ║
║  8 ║ 8.527 ║ 8.753 ║ 8.953 ║ 9.22  ║
║  9 ║ 8.533 ║ 8.757 ║ 8.957 ║ 9.23  ║
║ 10 ║ 8.54  ║ 8.76  ║ 8.96  ║ 9.24  ║
║ 11 ║ 8.546 ║ 8.772 ║ 8.976 ║ 9.246 ║
║ 12 ║ 8.552 ║ 8.784 ║ 8.992 ║ 9.252 ║
║ 13 ║ 8.558 ║ 8.796 ║ 9.008 ║ 9.258 ║
║ 14 ║ 8.564 ║ 8.808 ║ 9.024 ║ 9.264 ║
║ 15 ║ 8.57  ║ 8.82  ║ 9.04  ║ 9.27  ║
╚════╩═══════╩═══════╩═══════╩═══════╝

These numbers are rounded to three decimal places using SQL Server's default rounding. If you need to mimic e.g. Excel rounding, you would need to use a function that implements that specific algorithm.

Tested on SQL Server 2008 R2 with compatibility level 80, as indicated in your previous questions.

db<>fiddle (SQL Server 2012, earliest version available)