May 9, 2006 at 8:58 am
Hi,
I have two tables:
create table lut ( id1 INT, id2 INT )
insert x (id1,id2) values (1, 1)
insert x (id1,id2) values (1, 4)
insert x (id1,id2) values (1, 5)
insert x (id1,id2) values (2, 2)
insert x (id1,id2) values (2, 3)
go
create table data ( id1 INT, id2 INT )
insert x (id1,id2) values (1, 'word1')
insert x (id1,id2) values (2, 'word2')
insert x (id1,id2) values (3, 'word3')
insert x (id1,id2) values (4, 'word4')
insert x (id1,id2) values (5, 'word5')
go
I want to retrieve the data into two rows (with two columns):
id, concatenated string
1, 'word1' 'word4' 'word5'
2, 'word2' 'word3'
How do I do this in T-SQL??
Thank you very much!!
Erik
May 9, 2006 at 8:59 am
Sorry the second table should be:
create table data ( id1 INT, str VARCHAR)
insert x (id1,str ) values (1, 'word1')
insert x (id1,str ) values (2, 'word2')
insert x (id1,str ) values (3, 'word3')
insert x (id1,str ) values (4, 'word4')
insert x (id1,str ) values (5, 'word5')
go
Erik
May 9, 2006 at 11:49 am
Try this:
create table lut ( id1 INT, id2 INT )
insert lut (id1,id2) values (1, 1)
insert lut (id1,id2) values (1, 4)
insert lut (id1,id2) values (1, 5)
insert lut (id1,id2) values (2, 2)
insert lut (id1,id2) values (2, 3)
go
create table data ( id1 INT, str VARCHAR(5))
insert data (id1,str ) values (1, 'word1')
insert data (id1,str ) values (2, 'word2')
insert data (id1,str ) values (3, 'word3')
insert data (id1,str ) values (4, 'word4')
insert data (id1,str ) values (5, 'word5')
go
create function [dbo].fnConcat
(
@ID int
)
returns varchar(1000)
as
begin
declare @String varchar(1000)
select @String = coalesce(@String + ' ', '') + str
from lut
inner join data
on lut.id2 = data.id1
where lut.id1 = @ID
return (@String)
end
go
select dbo.fnConcat(x.id1)
from (select distinct id1 from lut) x
go
drop function fnConcat
go
drop table data
drop table lut
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply