Alan B Alan B - 7 months ago 22
SQL Question

Generating multiple data with SQL query

I have 2 tables as below

Product_Asset:

PAId Tracks
1 2
2 3


Product_Asset_Resource:

Id PAId TrackNumber
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3


I would like to know if I can generate the data in
product_asset_resource
table based on
product_asset
table using TSQL query (without complex cursor etc.)

For example, if the number of tracks in
product_asset
is 3 then I need to populate 3 rows in
product_asset_resource
with track numbers as 1,2,3

Answer

You can do this with the help of a Tally Table.

WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(N) AS(
    SELECT TOP(SELECT MAX(Tracks) FROM Product_Asset) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT
    Id  = ROW_NUMBER() OVER(ORDER BY pa.PAId, t.N),
    pa.PAId,
    TrackNumber = t.N
FROM Product_Asset pa
INNER JOIN CteTally t
    ON t.N <= pa.Tracks

ONLINE DEMO