Concatenating text lines into one

Hi,
I want to concatenate text lines into one line. What I have is an text_ID field with one or more seq no's. Each seq no has one line of text so there could be one text_ID with more than one seq no (ie more than one line of text),the result that I want is one ID with one line of text (concatenated).

The error that I keep getting is:

Server: Msg 512, Level 16, State 1, Procedure udf_Rates_Trans_GetLine, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This script that I am using is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--***********************************************
--** Create required functions
--***********************************************

--Returns the number of text lines for a note
CREATE FUNCTION udf_Rates_Trans_GetNumLines (@var2 int)
RETURNS int
AS
BEGIN
DECLARE @var1 int
Set @var1 = (SELECT max(pt_seq) FROM pt_text WHERE pt_text.pt_text_id = @var2)
return (@var1)
END
GO
--Retrieves the text contained within a particular line
CREATE FUNCTION udf_Rates_Trans_GetLine (@var1 int, @var2 int)
RETURNS varchar(100)
AS
BEGIN
RETURN( SELECT pt_text.pt_text
FROM pt_text
WHERE pt_text.pt_text_id = @var1
AND pt_text.pt_seq = @var2)
END
GO
--Stripes out the duplicate blanks that can occur within a text line
CREATE FUNCTION udf_StripBlanks (@var1 varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
RETURN(replace(replace(replace(@var1, ' ', ' ' + '¬'), '¬' + ' ', ''), '¬', '') )
END
GO
--Gets the next unique AccID
CREATE FUNCTION udf_Rates_Trans_GetNextID (@var1 int)
RETURNS int
AS
BEGIN
DECLARE @var3 int
SELECT @var3 = min(pt_text_ID)
FROM pt_text
WHERE pt_text.pt_text_ID > @var1
IF ISNULL(@var3,0) = 0
BEGIN
SET @var3 = 0
END
Return(@var3)

END
GO
--***********************************************
--** Create temp table
--***********************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp_RT_Comments]'))
drop table [dbo].[temp_RT_Comments]
GO

CREATE TABLE [dbo].[temp_RT_Comments] (
[text_ID] INT NULL,
[text_ln] varchar(1000) NULL
)

--***************************************
-- Declare Vars
--***************************************
DECLARE @minID INT,
@maxID INT,
@seqCnt INT,
@totalID INT,
@seqMax INT,
@currID INT,
@currSeq INT,
@currTxt varchar(60),
@ln2write varchar(1000)

--***************************************
-- Initialise Vars
--***************************************

SELECT @minID = min(pt_text_ID),
@maxID = max(pt_text_ID),
@totalID = count(pt_text_ID)
FROM
pt_text
SELECT @seqCnt = 1
SELECT @currSeq = 1
SELECT @ln2write = ''
SELECT @currID = @minID


--***********************************************
--** Main
--***********************************************

WHILE (@currID <= @maxID)
BEGIN
SET @seqMax = [dbo].[udf_Rates_Trans_GetNumLines](@currID)
WHILE @currSeq <= @seqMax
BEGIN
SELECT @ln2write = @ln2write + [dbo].[udf_Rates_Trans_GetLine](@currID, @currSeq)
SELECT @currSeq = @currSeq + 1
END
SET @ln2write = dbo.udf_StripBlanks(@ln2write)
INSERT [dbo].[temp_RT_Comments] VALUES (@currID,@ln2write)
SELECT @ln2write = ''

--Get next ID
SELECT @currID = [dbo].[udf_Rates_Trans_GetNextID](@currID)
SELECT @currSeq = 1
IF @currID = 0
BREAK
END

-------------------------------------------------------------
--clean up
-------------------------------------------------------------

GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_Rates_Trans_GetNumLines]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_Rates_Trans_GetNumLines]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_Rates_Trans_GetLine]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_Rates_Trans_GetLine]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_StripBlanks]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_StripBlanks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_Rates_Trans_GetNextID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_Rates_Trans_GetNextID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[temp_RT_Comments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [temp_RT_Comments]
GO

Can someone show me where I am going wrong?
Thanks

Greg

Well, the error points to your UDF udf_Rates_Trans_GetLine, to the following command:

SELECT pt_text.pt_text
FROM pt_text
WHERE pt_text.pt_text_id = @var1
AND pt_text.pt_seq = @var2

Given the situation, this query may return only one value. This can only work if (pt_text_id, pt_seq) is a unique key. To verify, run the following query:

SELECT pt_text.pt_text_id, pt_text.pt_seq, COUNT(*)
FROM pt_text
GROUP BY pt_text.pt_text_id, pt_text.pt_seq
HAVING COUNT(*) > 1

If you get any results for this query, then (pt_text_id, pt_seq) is not a unique key to the table.

Thanks Adriaan
I ran your script and there were erros. I'a little stuck...there are no keys on this table, this is what the tables looks like

2013,1,Advise people to be aware 95/96 sewerage charges will apply -
2013,2,see property file.
2013,3,
2013,4,LUMP SUM PAID FOR GLENBROOK SEWERAGE LOAN
2014,1,Advise people to be aware 95/96 sewerage charges will apply -
2014,2,see property file.
2014,3,
2014,4,THIS PROPERTY HAS ELECTED TO PAY THE SEWERAGE CONTRIBUTION CHARGE BY LOA
2014,5,THROUGHT THEIR RATES
2014,6,THE LOAN COMMENCED 1999/2000 YEAR RATES AND IS FOR 25 YEARS
2015,1,Advise people to be aware 95/96 sewerage charges will apply -
2015,2,see property file.
2015,3,
2015,4,LUMP SUM PAID FOR GLENBROOK SEWERAGE LOAN.
2016,1,Advise people to be aware 95/96 sewerage charges will apply -
2016,2,see property file.
2016,3,
2016,4,LUMP SUM PAID FOR GLENBROOK SEWERAGE LOAN.
2017,1,Advise people to be aware 95/96 sewerage charges will apply -
2017,2,see property file.
2017,3,
2017,4,LUMP SUM PAID FOR GLENBROOK SEWER LOAN.

as you can see there is more than one line of text per text_id. I would like to have the text_id and all the text lines concatenated into one line.

How should I write the UDF udf_Rates_Trans_GetLine?

You can concatenate multiple lines into a variable like this:

DECLARE @concat VARCHAR(8000)

SET @concat = ''

SELECT @concat = @concat + CASE WHEN LEN(@concat) = 0 THEN '' ELSE CHAR(13) + CHAR(10) END + mycolumn
FROM mytable
WHERE ......................

After that, @concat contains the concatenated lines, separated by a CRLF.

Note that in SQL 2000 there is a limit of 8000 characters (or 4000 for unicode).