Menu
I have the following table
ID | QUANTITY
------------
1 | 3
2 | 2
What I need is
ID | Ref No.
------------
1 | MyRef1
1 | MyRef2
1 | MyRef3
2 | AnotherRef1
2 | AnotherRef2
i.e. I need to generate Table B with the same number of rows as the quantity in A with an ascending ref no. on each row.
I can do it with cursors or UDFs but is there a more graceful solution?
982
We use TOP to limit that number to one greater than the largest Repeat value in your table. Then using those numbers we perform a cross join so that we have a row from the CTE for each number <= Repeat per job. And we add 1 on that side as well (this ensures that you also include the rows where Repeat = 0 ).
To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row# . You must move the ORDER BY clause up to the OVER clause. SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.
ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement.
I'll assume
Something like:
SELECT * INTO #TableA
FROM
(
SELECT 1 AS ID, 3 AS QUANTITY, 'MyRef' AS refColumn
UNION ALL
SELECT 2, 2, 'AnotherRef'
) T
;WITH Nbrs ( Number ) AS (
SELECT 1 UNION ALL
SELECT 1 + Number FROM Nbrs WHERE Number < 99
)
SELECT
A.ID, A.refColumn + CAST(N.Number AS varchar(10))
FROM
#TableA A
JOIN
Nbrs N ON N.Number <= A.QUANTITY
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
