SQLTeam.com | Weblogs | Forums

How generate Multiple rows based on quantity column value?


#1

I am trying to create labels based on a batches quantity. I have the query mostly compete except I can not figure out how to create multiple rows of the same data based on the number value in a column. I need the query to select from my temp table I've created the BatchID number and then generated rows based on the number value in the items column for create the duplicate rows in order to generate the labels. This is the query I have so far.

CREATE TABLE #Temp_Table
(
BatchID INT
,Received_Date DATE
,Batched_Date DATE
,Batch_Item_Count INT
,Location# INT
,Doc_Description VARCHAR(MAX)
)
INSERT INTO #Temp_Table

SELECT b.fstrBatchId,
b.fdtmReceived,
b.fdtmBatched,
b.fintItems,
L.fintStationnumber,
L.fstrDescription

FROM tblIL_BtcBatches b,
rfrIL_LocationNumber L

WHERE
b.fstrBatchId = '1514021901'
and L.LocationNumber = '219'

Select ROW_NUMBER() over (Partition by Batch_Item_Count order by Batch_Item_Count) AS Doc#,
BatchID AS BatchID From #Temp_Table

DROP TABLE #Temp_Table


#2

Currently the query gives only 1 row of results and should give multiple rows. I also need to format the rows to create a sequence that starts at 000 and then generates a sequence number for those rows. So if I have 12 items in a batch the query generates 12 rows with same BatchID and then runs a a sequence to generate document numbers starting from 0 formatted to look like 000, 001, 002, 003, 004 and so until you reach 011 which would be 12 items in the batch. Sorry I didn't type this before forgot about the formatted sequence number needed on the labels.


#3

You didn't provide any sample data, so I assume you won't take offense if I do it for you.

CREATE TABLE #Temp_Table
(
BatchID INT
,Received_Date DATE
,Batched_Date DATE
,Batch_Item_Count INT
,Location# INT
,Doc_Description VARCHAR(MAX)
)
INSERT INTO #Temp_Table
VALUES 
(1,GETDATE(), GETDATE(), 10, 1, 'A test Row'),
(2,GETDATE(), GETDATE(), 14, 1, 'Another test Row');

It's not clear to me if you have to reset the document number for each batch. If I understand correctly this is your requirement.

You also didn't tell us which column contains the quantity you want to use to multiply rows. I assumed it is Batch_Item_Count.

What you need here is a Tally Table: a table that contains numbers. If you don't have one, you can create it on the fly using some other table to pull the numbers from. In this case I used sys.all_columns.

WITH InlineTally AS (
	SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
	FROM sys.all_columns ac -- probably enough if quantities are not huge
)
SELECT *,
	document_number = RIGHT('000' + CAST(ROW_NUMBER() OVER(PARTITION BY BatchId ORDER BY N) -1 AS varchar(3)),3)
FROM dbo.#Temp_Table tt
INNER JOIN InlineTally AS it
	ON tt.Batch_Item_Count >= it.N

Hope this helps
Gianluca


#4

I will try this today, sorry about limited info. I am still new to SQL and trying to get the hang of programming. Thank you for your response and no offense taken.


#5

Ok my query gives me these results.

BatchID | Batch_Item_Count

1514021901 | 12

I need results to look like this

BatchID I Batch_Item_Count

1514021901 | 000
1514021901 I 001
1514021901 I 002
1514021901 | 003
1514021901 | 004
1514021901 | 005
1514021901 | 006
1514021901 | 007
1514021901 | 008
1514021901 | 009
1514021901 | 010
1514021901 | 011

I hope this is a OK example.


#6

The query from spaghettidba works perfectly - if you want output exactly like you described, change

SELECT *,

to

SELECT tt.BatchID,

#7

It worked thank you.


#8

Thank you for the help, I was finally able to get the query to work when bitsmed suggestion, and I also found an error I had from when I tired to copy your script.