Possible to Multiply 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

SQL Server 2005 Forums

Transact-SQL (2005)

Possible to Multiply Rows?

Author

Topic

cash
Starting Member

6 Posts

Posted - 2008-07-04 : 00:33:47

Is it possible to return duplicate copies of a row, based on a value in a collumn of that row? Example:

This Row:

ID DESC QUANTITY

1 ITEM 3

Is it possible form a query to give me a result such as:

1 ITEM
1 ITEM
1 ITEM

Is this possible (or even useful)??

Thanks for your input!

-Cash

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-04 : 01:18:27

declare @t table ( a int, b varchar(40), c int)
insert into @t
select 1,'innovate',4 union all
select 2,'arrogant',2

declare @numbers table ( number int )
insert into @numbers
select number from master.dbo.spt_values where number between 0 and 2000 and type = 'p'
--select * from numbers

select a,b from @t t
inner join numbers n on ( number <= c )

cash
Starting Member

6 Posts

Posted - 2008-07-05 : 18:12:30

Thanks Raky! worked perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 00:23:44

quote:
Originally posted by raky

declare @t table ( a int, b varchar(40), c int)
insert into @t
select 1,'innovate',4 union all
select 2,'arrogant',2

declare @numbers table ( number int )
insert into @numbers
select number from master.dbo.spt_values where number between 0 and 2000 and type = 'p'
--select * from numbers

select a,b from @t t
inner join numbers n on ( number <= c )

no need of extra table. you can directly do it in a single query

select t.id,t.desc 
from yourtable t
inner join master.dbo.spt_values v
on v.type = 'p'
and v.number<t.qty

also make sure qty is less than 2048 (thats the max value of number having type p in spt_values in 2005)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-07 : 09:39:33

I think it makes sense to use a numbers table. I wouldn't want my application logic relying on systems tables that MS may change in future releases.

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-07 : 14:21:30

Unless Sommarskog gathers enough followers on his petition to have a standard numbers (tally) table when installing SQL Server...

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