| Author |
 Topic  |
|
|
ericny
Starting Member
2 Posts |
 Posted - 02/13/2007 : 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
Sweden
29156 Posts |
Posted - 02/13/2007 : 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)
Singapore
16769 Posts |
|
|
ericny
Starting Member
2 Posts |
Posted - 02/15/2007 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/15/2007 : 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" |
|
|
| |
Topic |
|
How to concatenate many rows into one
