Concatenating data of a field - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Concatenating data of a field

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-01-31 : 13:06:56

I have 3 tables t1(F1,F2,F3), t2(F2,F4) & t3(F1,F5)

create table #t1(F1 varchar(10),F2 varchar(10),F3 int) 
create table #t2(F1  varchar(10),F4 varchar(10)) 
create table #t3(F2 varchar(10),F5 varchar(10))

Insert into #t1 ('aa','bb',3)
Insert into #t1 ('dd','bb',17)
Insert into #t1 ('gg','cc',36)
Insert into #t1 ('jj','dd',24)

Insert into #t2 ('bb','BBBB')
Insert into #t2 ('cc','CCCC')
Insert into #t2 ('dd','DDDD')

Insert into #t3 ('aa','ZZZZZ')
Insert into #t3 ('aa','YYYYY')
Insert into #t3 ('gg','XXXXX')
Insert into #t3 ('jj','WWWWW')
Insert into #t3 ('jj','VVVVV')

What I want as the results:

aa	bb	BBBB	ZZZZ,YYYY	 	 3
dd	bb	BBBB	NULL			17
gg	cc	CCCC	XXXXX	 		24
jj	dd	DDDD	WWWWW,VVVVV	 	 3

My Query So far

Select #t1.F1, #t1.F2, #t2.F4, ........... , #t1.F3 
From #t1 
inner join #t2 on #t1.F2 = #t2.F2
inner join #t3 on #t1.F1 = #t3.F1

Please help me to build the query

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 13:26:13

[code]SELECT DISTINCT F1,
F2,
F4,
dbo.fnConcatString(F5) AS F5,
F3
FROM (
SELECT t1.F1,
t1.F2,
t2.F4,
t3.F5,
t1.F3
FROM #Table1 AS t1
LEFT JOIN #Table2 AS t2 ON t2.F1 = t1.F3
LEFT JOIN #Table3 AS t3 ON t3.F2 = t1.F1
) AS d[/code]The function dbo.fnConcatString can be found here at SQLTeam!
Do a search...

Peter Larsson
Helsingborg, Sweden

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-01-31 : 14:47:11

Thanks Peso

Found something similar to fnConcatString and done.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources