LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Tables with one-to-many relationship

Tables with one-to-many relationship

Options

Previous Topic · Next Topic

Franko_1a

Posted: Tuesday, October 14, 2008 7:48:03 AM

Rank: Newbie

Joined: 10/14/2008
Posts: 1
Points: 3

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 to look like this, without duplicating the acctID in Table3:

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

bklr

Posted: Tuesday, January 27, 2009 11:29:38 PM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

declare @tab table(acctID int,Ntext varchar(32))
insert into @tab 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'

declare @tab1 table(acctID int,field1 varchar(32),field2 varchar(32))
insert into @tab1 select
1, 'f1data1', 'f2data1' union all select
2, 'f1data2', 'f2data2' union all select
3, 'f1data3', 'f2data3'

select t.acctid,t.field1,t.field2, (select stuff((select ' ' + ntext from @tab where acctid = t.acctid for xml path('')),1,1,'')) as text
from @tab1 t

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.