Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 populate rows of data with input from a column

Author  Topic 

Warsha
Starting Member

4 Posts

Posted - 2015-03-18 : 15:23:59
Hi all,

I have a query that gives me info from a sales table.It is basically a range of serials sold per invoice.

eg.
name date description beginserial endserial qty
apple store 18-Mar-15 retail bags 1234 1237 4
The firts row is the column names.
Now i want instead of just one row of data 4 rows of data, like
apple store 18-Mar-15 retail bags 1234
apple store 18-Mar-15 retail bags 1235
apple store 18-Mar-15 retail bags 1236
apple store 18-Mar-15 retail bags 1237
The number of rows is 4 because endserial minus beginserial plus 1 is 4. That is the way to calculate the number of rows of data.
Is this possible?
I will really appreciate if anyone can help me.

Regards,

Randjana

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-19 : 02:11:39
[code]
;WITH numList
AS ( SELECT 0 as num
UNION ALL
SELECT num + 1
FROM numList
WHERE num < 50)
,SampleCTE
AS(SELECT 'apple store' AS name,'18-Mar-15'AS [date]
,'retail bags' AS description, 1234 AS beginserial,1237 AS endserial,4 AS qty)

SELECT
Name , Date , description
,beginserial + num as Serial
FROM SampleCTE
CROSS JOIN numList
WHERE
num<qty
[/code]

and the result set :
[code]
Name Date description Serial
apple store 18-Mar-15 retail bags 1234
apple store 18-Mar-15 retail bags 1235
apple store 18-Mar-15 retail bags 1236
apple store 18-Mar-15 retail bags 1237
[/code]


sabinWeb MCP
Go to Top of Page

Warsha
Starting Member

4 Posts

Posted - 2015-03-20 : 10:09:38
Hi stepson,

the code works as i want, but it doesn't work when the qty is greater then 100. Why is it like that?

even when I do "WHERE num < 300".

I get the following error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
please help.

Regards,

Warsha
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-20 : 11:03:22
add this piece of code
OPTION ( MaxRecursion 0) 
after where


sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-20 : 11:03:42
[code]
;WITH numList
AS ( SELECT 0 as num
UNION ALL
SELECT num + 1
FROM numList
WHERE num < 400)
,SampleCTE
AS(SELECT 'apple store' AS name,'18-Mar-15'AS [date]
,'retail bags' AS description, 1234 AS beginserial,1237 AS endserial,4 AS qty)

SELECT
Name , Date , description
,beginserial + num as Serial
FROM SampleCTE
CROSS JOIN numList
WHERE
num<qty
OPTION ( MaxRecursion 0)
[/code]


sabinWeb MCP
Go to Top of Page
   

- Advertisement -