Cartesian product sql query

Question

Post reply

Cartesian product sql query

maxcoder1@gmail.com

Say Hey Kid

Points: 684
More actions
June 24, 2011 at 12:28 am

#389832

I want to create cartesian product sql query.But,I'm getting the error.Where I wrong?
I'm using SQL Server 2005.Here is my error code.
Msg 102, Level 15, State 1, Line 10 Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ','.
alter table #Personel
(
Ad varchar(100)
)
alter table #Masa
(
MasaAdi varchar(100)
)
insert into #Personel values('deneme1'),('deneme2'),('deneme3')
insert into #Masa values('Masa1'),('masa2'),('masa3')
select Ad,MasaAdi from
(
select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
from (
select newid() as ID,Ad,MasaAdi from #Personel
left outer join #Masa on 1=1
)t
) t2 where Sira = 1 order by Ad

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

prashavhad SSC Enthusiast Points: 165 More actions · Answer 1

While inserting data into a table, you should remove starting and ending brackets between each column data.

Please see below for this.

insert into #Personel values('deneme1','deneme2','deneme3')

insert into #Masa values('Masa1','masa2','masa3')

maxcoder1@gmail.com Say Hey Kid Points: 684 More actions · Answer 2

prashavhad (6/24/2011)
While inserting data into a table, you should remove starting and ending brackets between each column data.
Please see below for this.
insert into #Personel values('deneme1','deneme2','deneme3')
insert into #Masa values('Masa1','masa2','masa3')

Thanks for your reply.I have a question that chaught my mind.for example, They are sitting different tables two times some people from personel list. For instance, e.g 1-deneme1 -masa2 , 2-deneme2-masa3 ,3-deneme1-masa1 How to provide to sit down separate to each table of each person?

Thanks in advance.

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

It's not easy to understand what you are asking - can you provide sample input data, with desired output?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

maxcoder1@gmail.com Say Hey Kid Points: 684 More actions · Answer 4

Phil Parkin (6/24/2011)
It's not easy to understand what you are asking - can you provide sample input data, with desired output?

it's creating the result as shown below.

deneme1 - masa2

deneme2 - masa3

deneme1 - masa1

well,it's to be able to deploy to different tables two times the same personal.

e.g deneme1 - masa2

deneme1 - masa1

I don't want to do that.I want to do something as shown below.

e.g deneme1-masa2

deneme2-masa3

deneme3-masa1

So,I don't want to deploy to different tables two times the same personal.

drew.allen SSC Guru Points: 76997 More actions · Answer 5

Selahattin SADOGLU (6/24/2011)
insert into #Personel values('deneme1'),('deneme2'),('deneme3') insert into #Masa values('Masa1'),('masa2'),('masa3')

This syntax was introduced in SQL 2008. Since you're posting in the SQL 2005 forum and you're getting error messages about the commas, I assume that you're trying to run this on a SQL 2005 database. If so, you'll need to rewrite it with SQL 2005 syntax.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA