December 20, 2006 at 6:19 am
I have two tables : Master and History. Whenever a record in Master table is changed, the old version is sent to the History table along with Comments. So the History table will have 1 or more records for each record in Master table.
I need to have all the comments (from History table) for a particular record from Master concatenated together (with line breaks) for display.
Is there any other way other than using cursors or while loop to do this?
Any ideas/suggestions will be highly appreciated!
Geetali Sodhi
December 20, 2006 at 8:47 am
Try a UDF
-- Set up Test Scenario
use pubs
Create table mytable (pk int identity, [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
where pk = 3
GO
drop table Mytable
DROP FUNCTION fn_ConcatStrings
December 20, 2006 at 11:34 pm
Thanks a lot Ray. It took only 5 mins to implement this.
Geetali Sodhi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply