join

[jannat]

Hi All,

I need to do this ASAP. Please help.

I have a table which has three columns.
1. vendor_no
2. vendor_type
3. vendor_line
the data for each vendor_no can be upto six times I.e.

aaaaa 1 test
aaaaa 2 test1
aaaaa 3 test2
bbbbb 1 test3
bbbbb 2 test4
bbbbb 3 test5
bbbbb 4 test6
bbbbb 5 test7
bbbbb 6 test8
I need to load these data into another table which looks like this
1. vendo_no
2. Vendor_line1
3. Vendor_line2
4. Vendor_line3
5. Vendor_line4
5. Vendor_line5
6. Vendor_line6

The result should look like:

aaaaa test1 test2
bbbbb test3 test4 test5 test6 test7 test8

Can anyone help me with how to do it with a select statement by joining the same tables 6 times (may be, I am not sure) instead of writting a SP with cursors?

Any help will be greatly appreciated.

Thanks

[MAK]

create table ven (vno varchar(1NULL),type int ,line varchar(1NULL))

insert into ven select 'aaaaa',' 1',' test'
insert into ven select 'aaaaa',' 2',' test1'
insert into ven select 'aaaaa',' 3',' test2'
insert into ven select 'bbbbb',' 1',' test3'
insert into ven select 'bbbbb',' 2',' test4'
insert into ven select 'bbbbb',' 3',' test5'
insert into ven select 'bbbbb',' 4',' test6'
insert into ven select 'bbbbb',' 5',' test7'
insert into ven select 'bbbbb',' 6',' test8'


SELECT vno,
max(CASE type WHEN 1 THEN line ELSE NULL END) AS v1,
max(CASE type WHEN 2 THEN line ELSE NULL END) AS v2,
max(CASE type WHEN 3 THEN line ELSE NULL END) AS v3,
max(CASE type WHEN 4 THEN line ELSE NULL END) AS v4,
max(CASE type WHEN 5 THEN line ELSE NULL END) AS v5,
max(CASE type WHEN 6 THEN line ELSE NULL END) AS v6
FROM ven
GROUP BY vno

[DeadManWalking]

Try this...



insert into table2(fields...)
(
select t1.vendor_no, t1.test1, t2.test2,...t6.test6 from
(select vendor_no, test1 from table1 where vendor_type=1) t1
inner join
(select test2 from table1 where vendor_type=2) t2
on t1.vendor_no = t2.vendor_no
inner join
(select test3 from table1 where vendor_type=3) t3
on t1.vendor_no = t3.vendor_no...inner join
(select test6 from table1 where vendor_type=6) t6
on t1.vendor_no = t6.vendor_no



I know this isn't the best way but it should work...

HTH

[MAK]

--For getting comma
set SET CONCAT_NULL_YIELDS_NULL off
go

select vno, replace(v1+','+v2+','+v3+','+v4+','+v5+','+v6 , ',,','') from (
SELECT vno,
max(CASE type WHEN 1 THEN line ELSE NULL END) AS v1,
max(CASE type WHEN 2 THEN line ELSE NULL END) AS v2,
max(CASE type WHEN 3 THEN line ELSE NULL END) AS v3,
max(CASE type WHEN 4 THEN line ELSE NULL END) AS v4,
max(CASE type WHEN 5 THEN line ELSE NULL END) AS v5,
max(CASE type WHEN 6 THEN line ELSE NULL END) AS v6
FROM ven
GROUP BY vno) as x

[jannat]

Thank you Mak, as I said you the best. Always...........

[MAK]

really?

Where is my $120/hr contract in Queens.

hehehehehehe. Just kiddin.....

[jannat]

Soon Mak, it's on it's way.......