I have a table with 3 col and 4 row.
Col1 Col2 Col3
Row1 A1 B1 1
Row2 A2 B2 0
Row3 A3 B3 3
Row4 A4 B4 1
A select * from [table] returns:
A1 B1 1
A2 B2 0
A3 B3 3
A4 B4 1
I Want a select that give:
A1 B1 1
A3 B3 3
A3 B3 3
A3 B3 3
A4 B4 1
Col3 gives the number of row return.
Start with a numbers table... a table with one row for each number, going up as high as the largest possible value in your Col3. It will look something like this:
Table: Numbers
Value ----- 1 2 3 4 5 ...
Then you you can JOIN to this table using an inequality:
SELECT Col1, Col2, Col3
FROM [table] t
INNER JOIN NUMBERS n ON n.Value <= t.Col3
This will make your Row3 value match to the Numbers table 3 times, duplicating that row in the results, whereas the Row2 value won't match any records from the Numbers table, removing it from the results.
There are several options for generating a Numbers table you can look at here:
What is the best way to create and populate a numbers table?
With the Option 6 from that question:
WITH Numbers AS (
SELECT TOP 10000 row_number() over(order by t1.number) as [Value]
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
)
SELECT Col1, Col2, Col3
FROM [table] t
INNER JOIN NUMBERS n ON n.Value <= t.Col3
Note that this is overkill for your sample data, which only goes to 3. For anything less than about 50, you could just hard-code the table. I'm assuming your real data goes much higher.
