Generating Lines based on a value from a column in another table

Question

I have the following table:

EventID=00002,DocumentID=0005,EventDesc=ItemsReceived

I have the quantity in another table

DocumentID=0005,Qty=20

I want to generate a result of 20 lines (depending on the quantity) with an auto generated column which will have a sequence of:

ITEM_TAG_001,
ITEM_TAG_002,
ITEM_TAG_003,
ITEM_TAG_004,
..
ITEM_TAG_020

Ed Bangga · Accepted Answer · 2019-09-23T07:25:22.800

1

Here's your sql query.

with cte as (
    select 1 as ctr, t2.Qty, t1.EventID, t1.DocumentId, t1.EventDesc from tableA t1  
    inner join tableB t2 on t2.DocumentId = t1.DocumentId       
    union all 
    select ctr + 1, Qty, EventID, DocumentId, EventDesc  from cte
    where ctr <= Qty
)select *, concat('ITEM_TAG_', right('000'+ cast(ctr AS varchar(3)),3)) from cte
option (maxrecursion 0);

Output:

edited Sep 23 '19 at 07:25

answered Sep 23 '19 at 07:01

Ed Bangga

12,879
4
16
30

Using `ctr <= Qty` will count one step to much... Rather use `ctr < Qty` – Shnugo Sep 23 '19 at 07:19
Thanks @Shnugo, but i started @ ctr = 1 – Ed Bangga Sep 23 '19 at 07:20

Shnugo · Answer 2 · 2019-09-23T07:30:06.950

Best is to introduce a numbers table, very handsome in many places...

Something along:

Create some test data:

DECLARE @MockNumbers TABLE(Number BIGINT);
DECLARE @YourTable1 TABLE(DocumentID INT,ItemTag VARCHAR(100),SomeText VARCHAR(100));
DECLARE @YourTable2 TABLE(DocumentID INT, Qty INT);

INSERT INTO @MockNumbers SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values;
INSERT INTO @YourTable1 VALUES(1,'FirstItem','qty 5'),(2,'SecondItem','qty 7');
INSERT INTO @YourTable2 VALUES(1,5),                     (2,7);

--The query

SELECT CONCAT(t1.ItemTag,'_',REPLACE(STR(A.Number,3),' ','0'))
FROM @YourTable1 t1
INNER JOIN @YourTable2 t2 ON t1.DocumentID=t2.DocumentID
CROSS APPLY(SELECT Number FROM @MockNumbers WHERE Number BETWEEN 1 AND t2.Qty) A;

The result

FirstItem_001
FirstItem_002
[...]
FirstItem_005
SecondItem_001
SecondItem_002
[...]
SecondItem_007

The idea in short:

We use an INNER JOIN to get the quantity joined to the item.

Now we use APPLY, which is a row-wise action, to bind as many rows to the set, as we need it.

The first item will return with 5 lines, the second with 7. And the trick with STR() and REPLACE() is one way to create a padded number. You might use FORMAT() (v2012+), but this is working rather slowly...

The table @MockNumbers is a declared table variable containing a list of numbers from 1 to 100. This answer provides an example how to create a pyhsical numbers and date table. Any database should have such a table...

If you don't want to create a numbers table, you can search for a tally table or tally on the fly. There are many answers showing approaches how to create a list of running numbers...a

Generating Lines based on a value from a column in another table

2 Answers2