Concatenating text lines into one | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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?
Where do you want to show data? http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
Madhivanan Failing to plan is Planning to fail
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).
]]>