populate rows of data with input from a column

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources