How to concatenate many rows into one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to concatenate many rows into one

Author

Topic

ericny
Starting Member

2 Posts

Posted - 2007-02-13 : 00:45:22

I have data that looks like this:

ID Value
1 Descr1
1 Descr2
1 Descr3

where Descr could range from 1 to 100 for each ID

The result set I need is:
Descr1,Descr2,Desc3...etc.
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 00:59:04

Please remember that these concatenated strings only can hold 8000 characters.

declare @s varchar(8000)

select @s = left(isnull(@s + ',', '') + x.value, 8000)
from (select top 100 percent distinct value from yourtable1 order by value) as x

select @s

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 01:00:57

also see here http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

ericny
Starting Member

2 Posts

Posted - 2007-02-15 : 23:30:18

Thank you.

Basically I came up with the following:

DROP FUNCTION dbo.ConcatDescr
go

CREATE FUNCTION dbo.ConcatDescr(@TXRCODE CHAR(8))
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @Output VARCHAR(300)
SET @Output = ''
SELECT @Output = CASE @Output
WHEN '' THEN MON_TEXT
ELSE @Output + ', ' + MON_TEXT
END
FROM PCLONG
WHERE TXRCODE = @TXRCODE
order by MON_PCH

RETURN @Output
END
GO

SELECT TXRCODE, dbo.ConcatDescr(TXRCODE)
FROM PCLONG
WHERE TXRCODE = '01100008'

The code above works to concatenate lines into one however it truncates data after 256 characters. I looked in help and it says that varchar can be up to 8000 chars. Is there something I am doing wrong?

Thank you again.

EricNY
www.zipdrugs.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-15 : 23:33:42

"Is there something I am doing wrong?"

Yes, but it has nothing to do with your query. In Query Analyzer, Tools, Options, Results tab, set Maximum characters per column to some higher value, say, 8192.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources