Distinct merging multiple values from same field

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)

Distinct merging multiple values from same field

Author

Topic

sqlisamyth
Starting Member

1 Post

Posted - 2010-10-12 : 08:43:56

Hi all,

I'd need to select and merge values (distinct) from the same field of multiple selected rows from the table b to join table a.

Let's say I've got Table A and B as below.

<Table A>
ID Name
----------
1 John
2 Susan

<Table B>
ParentID Asset
----------------
1 house
1 house
1 car
2 tv
2 dvd

When I do the intended join selection, I'd need to see the result below:

ID Name Asset
-------------------
1 John house,car
2 Susan tv, dvd

The scenario has been bothering me quite a while, really need some help here.

Please help with solution and script details on if this could be done with a single query or need of a stored procedure call.

Thanks in advance,
Steve

mikgri
Starting Member

39 Posts

Posted - 2010-10-12 : 13:46:48

you can try this (it is first to came to my mind):

if object_id('tempdb.dbo.#tbl') is not null drop table #tbl;

create table #tbl (id int, name varchar(50), asset varchar(50));

DECLARE cur CURSOR
FOR SELECT distinct parentid, asset FROM tableB

DECLARE @id int, @asset varchar(50), @previd int, @strasset varchar(2000), @sql varchar(2000)
set @previd=0
set @strasset=''

OPEN cur

FETCH NEXT FROM cur INTO @id, @asset
WHILE @@fetch_status = 0
BEGIN
if @previd=0 set @previd=@id
if @id<>@previd
begin
insert #tbl (id, name, asset)
select @previd, name, @strasset from tableA where id=@previd
set @strasset=@asset
set @previd=@id
end
else
begin
select @strasset=case @strasset when '' then @asset else @strasset+','+@asset end
end
FETCH NEXT FROM cur INTO @id, @asset
END

CLOSE cur
DEALLOCATE cur

--insert last record
insert #tbl (id, name, asset)
select @previd, name, @strasset from tableA where id=@previd
set @strasset=''
set @previd=@id

GO

select * from #tbl

tm
Posting Yak Master

160 Posts

Posted - 2010-10-12 : 15:14:58

Please refer to original posting from Peso ..
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254[/url]

--=================
declare @A table (ID INT, [name] VARCHAR(100))
declare @B table (ParentID INT, Asset VARCHAR(100))

insert into @A
select 1, 'John'
union
select 2, 'Susan'

insert into @B
select 1, 'house'
union all
select 1, 'house'
union all
select 1, 'car'
union all
select 2, 'tv'
union all
select 2, 'dvd'

;with CTE (ID, [Name], Asset) AS
(
select distinct A.ID, A.Name, B.Asset
from @A A inner join
@B B
on A.ID = B.ParentID
)

-- Show the expected output
-- Sample expression is modified from original posting (above link)..

SELECT DISTINCT s1.ID, s1.Name,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.Asset FROM CTE AS s2 WHERE s2.ID = s1.ID AND s2.Name = s1.Name ORDER BY ',' + s2.Asset FOR XML PATH('')), 1, 1, '') AS Assets
FROM CTE AS s1
ORDER BY s1.ID, s1.Name

tm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources