How to select one Row twice or More

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 2000 Forums

SQL Server Development (2000)

How to select one Row twice or More

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2008-07-04 : 13:54:02

Hi, i would like to select the same record more than once
example

Tablex
Cod - Name
123 - John
124 - mary

I would like to to make a Select bring me this

123 - John
123 - John
123 - John
123 - John

the purpose is to make a mailing label but i need to repeat
john several times

tks
Clages

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-04 : 14:41:59

Well some of the things you can do are:
1. Create a UNION query. But the downside to this is that you have to repeat the sql over and over.

 
SELECT Cod, [Name] FROM TableX 
UNION ALL
SELECT Cod, [Name] FROM TableX WHERE [NAME] = 'John'
UNION ALL
SELECT Cod, [Name] FROM TableX WHERE [NAME] = 'John'
UNION ALL
SELECT Cod, [Name] FROM TableX WHERE [NAME] = 'John'

2. Another option would be to create a temporary table or a new table then do a loop to insert the data. Then use the temp table as the datasource for your mailing labels.

  
DECLARE @InsertNum INT 

SET @insertnum = 3

SELECT * INTO #tablex FROM tablex --create your temp table 

SELECT @insertnum

WHILE (@InsertNum <> 0 )
	BEGIN 
		INSERT INTO #Tablex SELECT Cod,[name] FROM Tablex;
		SET @insertNum = @InserTNum - 1
		Print @InsertNum
	END 

SELECT * FROM #tablex ORDER BY Cod

dallr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-04 : 15:04:44

Use CROSS JOIN.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-04 : 15:35:36

As Peso suggested, use Cross Join

declare @t table(Cod int, Name  varchar(2000))
insert into @t
select 123 ,'John' union all
select 124 ,'mary'

select 
	top 10 t1.* from @t t1 
	cross join master..spt_values t2
where t1.name='john'

Madhivanan

Failing to plan is Planning to fail

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-04 : 15:57:33

Interesting concept....

I am not getting any information in BOL about the (master..spt_values) table. Can anybody tell me what it does or provide some links to information on it?

Regards
Dallr

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-04 : 17:10:52

see this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102936

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-09 : 00:55:20

Thanks Sodeep!

Dallr

Clages1
Yak Posting Veteran

69 Posts

Posted - 2008-07-09 : 18:45:07

I see the point using cross join
but why use master..spt_values?
in fact i can use any table, since you are
'selecting top x' rows

i have test using other table and works fine.

Tks
Clages
by the way i am using cross join and i solved my problems

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 01:24:03

quote:
Originally posted by Clages1

I see the point using cross join
but why use master..spt_values?
in fact i can use any table, since you are
'selecting top x' rows

i have test using other table and works fine.

Tks
Clages
by the way i am using cross join and i solved my problems

no necessity that you should use spt_values. you can even create a count table yourselves and use it. Its just that spt_values is a standard table available in sql server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources