Multiplying records - 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)

Multiplying records

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-23 : 19:00:18

Is it possible to multiply the records?

example:

col1 col2
rec1 1
rec2 2

result:
col1 col2
rec1 1
rec1 1
rec1 1
rec2 2
rec2 2
rec2 2

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-23 : 19:04:15

You can use a CROSS JOIN to get a cartesian product.

What is your business requirement? Will it always be 3 rows for each?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-23 : 19:12:07

Yes, it always 3 rows for each..

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-23 : 23:19:41

Hi how it will get 3 times for each , i tried using outer apply, try it once.

declare @temp table (col1 varchar(32),col2 int)
insert into @temp
select 'rec1' ,1 union all
select 'rec2', 2

select t.col1,t.col2 from @Temp t
outer apply @temp t1

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-24 : 10:31:02

anyone here knows how to multiply records?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-24 : 10:33:15

Try following the first link in my signature. The better the question, the better the answer!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-24 : 10:54:30

Hi Don,

I know I have to explain in detailed my requirements. Does my example data above was not enough to understand my question? :)
The requirement is just to multiply every records or tripled every record. :)

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-24 : 10:56:20

Did you try what Nageswar9 suggested?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-24 : 11:00:18

Yeah, but it only doubled the records. :) I'm still trying to figure this out.

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-24 : 11:09:48

This is my way to resolve this.

Select field from tbl
union all
Select field from tbl
union all
Select field from tbl

Do you think it is fine to do this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources