Tuesday, March 27, 2012

Combining multiple rows into one

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.
Does someone have a query to do this?
Thank youselect case when Descr1=... end as Descr1, case when Descr2=... end as Descr2, case when Descr3=... end as Descr3 from <your table name>

my first contribution here, quite similar to what I was trying to do in a project, do correct me if it's wrong.|||I will not be able to use CASE since the values of Descr1 etc. are always different|||What i mean is that the values in Value column are always different and are unknown. Therefore i will not be able to use CASE|||Ta da!

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx|||this is pretty much what adam is doing, but it's more compact and mysterious the first time you see it:

http://sqlblindman.googlepages.com/creatingcomma-delimitedstrings

plus it's from a regular here. :)|||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.|||it is because of the length of the variable where u r putting the data...increase it and the Return as well

RETURNS VARCHAR(300)
......
DECLARE @.Output VARCHAR(300)|||Your data may also be getting truncated by Query Analyzer. Check the QA options and bump up the maximum character output enough to display your results.

No comments:

Post a Comment