SQL Server Performance

Concatenating text lines into one

Discussion in 'T-SQL Performance Tuning for Developers' started by gregalb, Jun 25, 2007.

  1. gregalb New Member

    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
  2. Adriaan New Member

    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.
  3. gregalb New Member

    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?
  4. Madhivanan Moderator

  5. Adriaan New Member

    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).

Share This Page