July 10, 2008 at 9:18 am
Hi
ID Name
35 Smith
35 Tape
35 Jon
36 Gap
36 Data
I would like the to query the table above so that each ID displays its associated name in a row by concatenating the Names as shown below
ID Name
35 Smith, Tape, Jon
36 Gap, Data
Can any one help me with this please.
Thanks
July 10, 2008 at 10:07 am
I have found a solution using a cursor but if anyone knows any other solution then please let me know
declare @temp table
(ID int,
Name varchar (20)
)
insert into @temp
(id,
name
)
SELECT distinct ID, '' as Name FROM Test_Felix
DECLARE @ID varchar(50), @Name varchar(50)
--Caching the content of the table into a cursor
DECLARE contact_cursor CURSOR FOR
SELECT ID, Name FROM Test_Felix
OPEN contact_cursor
-- Perform the first fetch.
FETCH NEXT FROM contact_cursor
into @ID, @Name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @temp
set NAME = NAME + ','+ @NAme where ID = @ID
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM contact_cursor
INTO @ID, @Name
END
CLOSE contact_cursor
DEALLOCATE contact_cursor
select right(Name,len(Name)-1) from @temp
GO
ID Name
1A,b,g
2c
3e
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply