How to produce quasi duplicates in a Select-CodePudding

Im working with MS SQL Server. I cannot explain it really good. I want to change my select statement such that it fills gaps that arent really in the datatable with duplicated values:

Assume following sample data:

col1	col2	col3
423	1	2
423	3	3

I want to fill up the missing col2 numbers which depends on the amount in col3, which would be a result like this:

col1	col2	col3
423	1	2
423	2	2
423	3	3
423	4	3
423	5	3

Its like reverse engineering a select distinct col1,col3

Code:

CREATE TABLE [testtable](
    [col1] [smallint] NOT NULL,
    [col2] [smallint] NOT NULL,
    [col3] [smallint] NULL
)
GO

INSERT INTO testtable
VALUES 
   (423, 1, 2),
   (423, 3, 3)

If its really complicated dont bother solving it, but maybe there is an easy way.

CodePudding user response：

If I understand the question correctly, a recursive CTE is a possible solution:

; WITH rCTE AS (
   SELECT 1 AS id, t.col1, t.col2, t.col3
   FROM testtable t
   UNION ALL
   SELECT r.id   1, r.col1, CONVERT(smallint, r.col2   1), r.col3
   FROM rCTE r
   WHERE r.id   1 <= r.col3
) 
SELECT col1, col2, col3
FROM rCTE