joining 2 tables with 1 to many relationship

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

SQL Server 2005 Forums

Transact-SQL (2005)

joining 2 tables with 1 to many relationship

Author

Topic

Franco_1
Starting Member

2 Posts

Posted - 2008-10-10 : 14:56:24

table1
acctID Ntext
1 hello
1 This
1 Is
2 notReal
2 What
3 Final

table2
acctID field1 field2
1 f1data1 f2data1
2 f1data2 f2data2
3 f1data3 f2data3

I want to insert data into another table. I need to concatenate all fields from both tables into one column to look like this:

Table3
acctID allCollsTable1and2
1 f1data1 f2data1 helloThisIs
2 f1data2 f2data2 notReal What
3 f1data3 f2data3 Final

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-10 : 16:48:30

drop table #table1
GO
drop table #table2
GO
create table #table1
(acctID int, Ntext varchar(50));

insert into #table1
select 1 , 'hello'
UNION ALL
select 1 , 'This'
UNION ALL
select 1 , 'Is'
UNION ALL
select 2 , 'notReal'
UNION ALL
select 2 , 'What'
UNION ALL
select 3 , 'Final';

create table #table2
(acctID int, field1 varchar(50), field2 varchar(50));

insert into #table2
select 1 , 'f1data1' , 'f2data1'
UNION ALL
select 2 , 'f1data2' , 'f2data2'
UNION ALL
select 3 , 'f1data3' , 'f2data3';

declare @str_sql varchar(8000), @i int, @nt varchar(50), @catstr varchar(8000)

set @str_sql = ''

declare cur cursor for
select distinct acctid from #table1

open cur

fetch next from cur
into @i

while @@fetch_status = 0
begin
set @catstr = ''

declare cur1 cursor for
select [ntext] from #table1 where acctid = @i

open cur1

fetch next from cur1
into @nt

while @@fetch_status = 0
begin

set @catstr = @catstr + ' ' + @nt

fetch next from cur1 into @nt

end

close cur1
deallocate cur1
print ltrim(rtrim(@catstr))
set @str_sql = @str_sql + ' select *, '''+ltrim(rtrim(@catstr))+''' as [1and2] from #table2 where acctid = '+ cast(@i as varchar)

fetch next from cur into @i
IF @@fetch_status = 0
begin
set @str_sql = @str_sql + ' UNION ALL '
end
end
print @str_sql
exec(@str_sql)
close cur
deallocate cur

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-10 : 16:50:32

[code]acctID field1 field2 1and2
----------- ----------- ---------- ----------------------
1 f1data1 f2data1 hello This Is
2 f1data2 f2data2 notReal What
3 f1data3 f2data3 Final
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-11 : 01:26:25

han why use cursor for this? the below is enough

SELECT t1.acctID,t2.field1,t2.field2,t1.Ntext
FROM table2 t2
INNER JOIN (SELECT m.acctID,
            vl.ValList AS Ntext
            FROM (SELECT DISTINCT acctID FROM table1) m
            CROSS APPLY (SELECT Ntext+ ' ' AS [text()]
                         FROM table1
                         WHERE acctID=m.acctID
                         FOR XML PATH('')vl(ValList)
            )t1
ON t1.acctID=t2.acctID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources