how to show record twice time - 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 2000 Forums

Transact-SQL (2000)

how to show record twice time

Author

Topic

myheart46
Starting Member

13 Posts

Posted - 2003-11-26 : 03:52:13

i want to select data that show same record twice
-->
id name
--- --------
1 john
1 john
2 ken
2 ken
3 jim
3 jim
.....

i want to repeat record follow user sometime 2 or 3,4
someone help me plzz

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-11-26 : 04:37:03

[code]
SELECT id, name
UNION ALL
SELECT id, name
UNION ALL
SELECT id, name
ORDER BY id, name
[/code]

________________
Make love not war!

myheart46
Starting Member

13 Posts

Posted - 2003-11-26 : 05:16:38

ohh thank yoou very much... :P
but sometime user need 3 4 5 6 and more
do u know 'select code' that has variable ?
example...
select id,name
from table
where :copy =7

but anyway thx so much

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-26 : 07:16:47

Try something like this, you will need a table of numbers (which is moo here)


create table moo (blah int null)
insert into moo values (1)
insert into moo values (2)
insert into moo values (3)

create table moo2 (id int identity, name varchar (10) null)
insert into moo2 values ('john')
insert into moo2 values ('ken')
insert into moo2 values ('jim')

declare @moo int
select @moo = 3
select id, name from moo2
left outer join
  moo
on blah < @moo

-------
Moo. :)

mkbosmans
Starting Member

15 Posts

Posted - 2003-11-26 : 07:19:33

Why would you even want to do this inside the SQL server?
Looks like a job for your presentation layer.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-26 : 08:39:05

Mr mist has got the answer, in MOO ...

But don't try to hide it with an OUTER JOIN ... that's really a CROSS JOIN baby! (i.e., there is no relationship at all between the numbers table and the data)

- Jeff

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-26 : 10:18:14

I thought it was a cross join at first, but I could not figure how to get the vaiable involved.

-------
Moo. :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-26 : 10:57:00

Just use a WHERE clause.

SELECT id, name
FROM moo2
CROSS JOIN
moo
WHERE blah < @moo

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-26 : 13:32:20

quote:
Originally posted by mkbosmans

Why would you even want to do this inside the SQL server?
Looks like a job for your presentation layer.

Personally, I like this question...

Any answer?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-26 : 14:11:57

honestly, i think we're not even answering the question. Looking at the original post, it sounds more like: return rows that ARE duplicated at least N times, as opposed to returning N duplicates of existing rows.

i.e., maybe:

SELECT ID
FROM tbl
GROUP BY ID
HAVING COUNT(*) > @n

is what he/she is after really?

- Jeff

mkbosmans
Starting Member

15 Posts

Posted - 2003-11-26 : 16:42:03

Doesn't seem like that, while myheart46 the union all query.
Off course it can be done in a cross join, but I'm still wondering why anyone would wan't to do this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources