Split rows - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Split rows

Author

Topic

Mauricio Moreno
Starting Member

18 Posts

Posted - 2007-08-21 : 15:29:02

not sure if this is possible...

but lets say i make a select like

select products, stock from table

and my rs is

chair | 1
couch | 3
lamp | 2

is there anyway in the select to make any row that has stock of more than 1 to make a new row... so my rs would come back as

chair
couch
couch
couch
lamp
lamp

Any info would be helpful...

Thanks,

~ moe

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 15:48:44

Not sure why you would want to do this...but you need a numbers table


DECLARE @x table(product varchar(255), stock int)
INSERT INTO @x(product,stock)
SELECT 'chair', 1 UNION ALL
SELECT 'couch', 3 UNION ALL
SELECT 'lamp',  2

select *, product from @x x INNER JOIN numbers n on x.stock+1 > n

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-22 : 11:40:12

Well, did this work for you?

Need to know what a numbers table is?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sbalaji
Starting Member

48 Posts

Posted - 2007-08-23 : 01:55:25

nice way to use numbers table,
numbers tables should contain values from 1 to max(stock)

Is there any way we can populate the numbers table dynamically,w.r.t to max stock value???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 03:34:30

Yes, use the function made by MVJ.
F_TABLE_NUMBER_RANGE

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources