sql statement
-
Hi,
I have a table like below
id col 1 aaa 2 bbb 3 ccc 3 ddd 4 eee 5 fff 5 ggg 6 hhh I need a result set as below
col aaa bbb ccc,ddd eee fff,ggg hhh Is that possible with select statement, thanks for your help.
-
It could be done by looping the records, but not in a single select.
-
or create a function.
use pubs
Create table mytable ([ID] int, col varchar(20))
insert into mytable ([id],col)
select 1, 'aaa' union
select 2, 'bbb' union
select 3, 'ccc' union
select 3, 'ddd' union
select 4, 'eee' union
select 5, 'fff' union
select 5, 'ggg' union
select 6, 'hhh'
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fn_ConcatStrings')
DROP FUNCTION fn_ConcatStrings
GO
CREATE FUNCTION fn_ConcatStrings
(@ID int)
RETURNS varchar(500)
AS
BEGIN
declare @String varchar(500)
select @String = coalesce(@String,'') + Col + ','
from mytable
where [Id] = @ID
if Right(@String,1) = ','
set @String = substring(@String,1,len(@String) -1)
return @String
END
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT [id], dbo.fn_ConcatStrings ([id])
from mytable
GO
drop table Mytable
DROP FUNCTION fn_ConcatStrings
-
Ray,
Thanks, but I don't know which id is repeating, need all records from that table but repeated one's should concatinate.
-
The function provided by Ray works. You need to add a GROUP BY to the SELECT statement though.
EG:
SELECT [id], dbo.fn_ConcatStrings ([id]) from mytable group by id
will give you
1 aaa 2 bbb 3 ccc,ddd 4 eee 5 fff,ggg 6 hhh
--------------------
Colt 45 - the original point and click interface
-
It worked, Thank you very much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply