June 24, 2011 at 12:28 am
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
June 24, 2011 at 12:59 am
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')
June 24, 2011 at 1:51 am
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.
June 24, 2011 at 2:05 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 24, 2011 at 5:20 am
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.
June 24, 2011 at 7:42 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply