June 26, 2002 at 2:26 pm
Hi all,
I am in a fix. I am not experienced in SQL Server. I need to accomplish this code in SQL Server like I did in Oracle. I will first describe my problem and then the solution I have for it in Oracle.
I was hoping you gurus can help me out here.
the problem:
I have two tables for example:
tablename: table1
ID_NO FULL_NAME
1 MARY
2 BOB
3 KIM
4 EDDIE
tablename: table2
ID_NO LIKES
1 FLOWERS
1 SUNSHINE
2 GADGETS
2 SUPER MODELS
2 FAST CARS
3 BOATS
3 FLOWERS
3 LONG NAILS
3 SKIRTS
4 GADGETS
4 HUMMER
4 RV
4 DOGS
4 WINE
Result should be somewhat like this. Basically distinct records of table1 and last column with child records from table2:
ID_NO FULL_NAME LIKES
1 MARY FLOWERS|SUNSHINE
2 BOB GADGETS|SUPER MODELS|FAST
CARS
Oracle Solution:
SQL> CREATE TYPE arch_test_type AS TABLE OF VARCHAR2(55);
Then run the query:
Query:
SELECT rtrim(e.full_name) NAME, CAST(MULTISET(SELECT rtrim(p.likes) FROM arch_test2 p WHERE p.id_no = e.id_no) AS arch_test_type) LIKES FROM arch_test1 e
NAME LIKES
------- ------------------------------------
ARCHITA ARCH_TEST_TYPE('FLOWERS', 'SUNSHINE')
VAMSHI ARCH_TEST_TYPE('GADGETS', 'SUPER MODELS', 'FAST CARS')
KIM ARCH_TEST_TYPE('BOATS', 'FLOWERS', 'LONG NAILS', 'SKIRTS')
EDDIE ARCH_TEST_TYPE('GADGETS', 'HUMMER', 'RV', 'DOGS', 'ICKY WINE')
I am sure there is a solution to my problem. I will obliged if you guys can show me the way.
Thanks,
Archita
June 26, 2002 at 6:13 pm
Here's a quick N dirty solution. May be not that dirty really......
In the following code, tab1 is your first table with id's and peoples names and tab2 is the second table with what they like, e.g.
Hummers, Flowers etc.
set nocount on
declare @count smallint
declare @likes varchar(255)
create table #WhoLikesWhat ( id int, Name varchar(50), likes varchar(255) )
select @count = count(*) from tab1
while @count > 0
begin
set @likes = ''
select @likes = @likes + name + '|' from tab2
where id = @count
Insert into #WhoLikesWhat (id, [Name], Likes )
select [id], [name], @likes from tab1 where [id] = @count
set @count = @count-1
end
select * from #WhoLikesWhat order by id
Let me know if it works for you...
Bharat.
June 26, 2002 at 6:51 pm
Just a clarification...
In the sql that I wrote I call the first column in both tab1 and tab2 as 'Id' instead of 'Id_no' and the second columns as 'name' instead of 'full_name' and 'likes'. you would need to synch the names if you run the query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply