what have I use sql join, please

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

General SQL Server Forums

New to SQL Server Programming

what have I use sql join, please

Author

Topic

bewek
Starting Member

7 Posts

Posted - 2010-06-12 : 01:42:40

Hi,
I need solve one urgent problem. I have two table.

table1
(columns)
car owner
(values)
audi john
audi paul
seat george
seat mike
seat miranda
(one car had maximal 6 owners)

table2
(columns)
owner card
(values)
john 1234
paul 3487
george 7809
mike 3849
miranda 3984

I need get by SQL this:

car card(1) card(2) ... card (6)
audi 1234 3487
seat 7809 3849 3984

Please for help, very thank you

Sachin.Nand

2937 Posts

Posted - 2010-06-12 : 04:52:43

[code]
declare @tbl as table(owner varchar(40),card int)
insert into @tbl
select 'john',1234 union all
select 'paul', 3487 union all
select 'george', 7809 union all
select 'mike',3849 union all
select 'miranda', 3984

declare @tbl1 as table(car varchar(40),owner varchar(40))
insert into @tbl1
select 'audi','john' union all
select 'audi', 'paul' union all
select 'seat', 'george' union all
select 'seat','mike' union all
select 'seat', 'miranda'

select car,
MAX(case when rid=1 then card end) as card1,
MAX(case when rid=2 then card end) as card2,
MAX(case when rid=3 then card end) as card3,
MAX(case when rid=4 then card end) as card4,
MAX(case when rid=5 then card end) as card5,
MAX(case when rid=6 then card end) as card6 from
(
select t.card,t1.car,ROW_NUMBER()over(partition by car order by card)as rid
from @tbl t inner join @tbl1 t1 on t.owner=t1.owner
)t group by car
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

bewek
Starting Member

7 Posts

Posted - 2010-06-12 : 05:50:39

Very thank you for help but my program Toad for Oracle 10 say me this:

ORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

How can I solve it? Thank you

Sachin.Nand

2937 Posts

Posted - 2010-06-12 : 06:09:49

Just replace the part marked in red with your actual table names


select car,
MAX(case when rid=1 then card end) as card1,
MAX(case when rid=2 then card end) as card2,
MAX(case when rid=3 then card end) as card3,
MAX(case when rid=4 then card end) as card4,
MAX(case when rid=5 then card end) as card5,
MAX(case when rid=6 then card end) as card6 from
(
select t.card,t1.car,ROW_NUMBER()over(partition by car order by card)as rid
 from table1 t inner join table2 t1 on t.owner=t1.owner
 )t group by car

Also this is an SQL Server forum.All the questions related only to SQL Server are to be posted here.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

bewek
Starting Member

7 Posts

Posted - 2010-06-12 : 06:41:27

Already it work ok.

VERY VERY VERY thank you. : )
You're great.

Have nice day.

Georg
Czech Republic

Sachin.Nand

2937 Posts

Posted - 2010-06-12 : 06:43:58