Concatenate several records into one
-
I have a table in MS SQL Server 2005 containing two fields :- TicketId and Narrative The data within the table may look as follows TicketId Narrative -------- --------- 302523 FARMS 302523 UNIT 8420 KGS 302524 VEHICLE BREAKDOWN 302525 REPAIR SCHEDULE Notice the first two records have the same ticket Id therefore are related. I need setup a view the returns 1 record per ticket, concatenating related narratives together e.g. TicketId Narrative -------- --------- 302523 FARMS UNIT 8420 KG 302524 VEHICLE BREAKDOWN 302525 REPAIR SCHEDULE Any help much appriciated Steve Jowett
-
I have a table in MS SQL Server 2005 containing two fields :- TicketId and Narrative The data within the table may look as follows TicketId Narrative -------- --------- 302523 FARMS 302523 UNIT 8420 KGS 302524 VEHICLE BREAKDOWN 302525 REPAIR SCHEDULE Notice the first two records have the same ticket Id therefore are related. I need setup a view the returns 1 record per ticket, concatenating related narratives together e.g. TicketId Narrative -------- --------- 302523 FARMS UNIT 8420 KG 302524 VEHICLE BREAKDOWN 302525 REPAIR SCHEDULE Any help much appriciated Steve Jowett
use function create function abc(@TicketId char(x)) returns char(xxx) as begin declare @ret char(xxxx) declare @tmp table ( .... ) insert into @tmp (...) select .... while ... begin ... set @ret = @ret + Narrative end return @ret end select TicketId,dbo.abc(ticketid) 'Narrative' from yourtable
-
use function create function abc(@TicketId char(x)) returns char(xxx) as begin declare @ret char(xxxx) declare @tmp table ( .... ) insert into @tmp (...) select .... while ... begin ... set @ret = @ret + Narrative end return @ret end select TicketId,dbo.abc(ticketid) 'Narrative' from yourtable
MY finalized function, if anyone is interested :-
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ConcatNotes](@TicketId BigInt) RETURNS NVarChar(4000) AS BEGIN -- Declare the return variable here DECLARE @Narrative NvarChar(4000) DECLARE @Notes NVarChar(2000) DECLARE notes_cursor CURSOR FOR SELECT [Text] FROM Notes WHERE TicketId = @TicketId ORDER BY NoteId ASC OPEN notes_cursor FETCH NEXT FROM notes_cursor INTO @Notes SET @Narrative = '' WHILE @@FETCH_STATUS = 0 BEGIN IF LEN(@Narrative) > 0 BEGIN SET @Narrative = @Narrative + ' ¦ ' END SET @Narrative = @Narrative + @Notes FETCH NEXT FROM notes_cursor INTO @Notes END CLOSE notes_cursor DEALLOCATE notes_cursor -- Return the result of the function RETURN LTRIM(@Narrative) ENDThen in my SELECT statementSELECT TicketId, ConcatNotes(TicketId) AS Narrative FROM TicketsThanks to Zhengdong Jin for pointing me in the right direction Steve Jowett