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.

Column troubles

I've got a table which goes something like this:
id usertext1 usertext2 usertext3 label1 label2
label3
123 12 12A12345 BPU
Charge
124 08B14501 8 Charge
BPU
.....
When I want to make a select statement I know which column has "Charge", but
I don't know where "BPU" is. This can be in a different column each time.
How can I make a select statement to find out in which column the "BPU" valu
e
is. Something like: select (something) where id='123'
I want to fit it into a webpage, but I have to get the right select statemen
t
first.
I pick up the "Charge" column from the page and want to set the first two
digits from the accompanying column (e.g. label2/usertext2) in the column
where the "BPU" is.
This is probably not very clear, but it's a bit difficult to explain.
So if you need further information to help me, please ask, because I really
need some help here.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1Hi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data
If you wish to select any rows that contains BPU in label1 or label2 use
something like
SELECT id,usertext1,usertext2,usertext3,label1,
label2
FROM MyTable
WHERE label1 = 'BPU' OR label2 = 'BPU'
If you want to select them in different orders
SELECT id,usertext1,usertext2,usertext3,label1,
label2
FROM MyTable
WHERE label1 = 'BPU'
UNION
SELECT id,usertext1,usertext2,usertext3,label2,
label1
FROM MyTable
WHERE label2 = 'BPU'
Therefore the column containing 'BPU' will always be the 5th column in the
result set.
John
"Rune Thandy via droptable.com" <u17273@.uwe> wrote in message
news:65efb7eb91074@.uwe...
> I've got a table which goes something like this:
>
> id usertext1 usertext2 usertext3 label1
> label2
> label3
> 123 12 12A12345 BPU
> Charge
> 124 08B14501 8 Charge
> BPU
> .....
>
> When I want to make a select statement I know which column has "Charge",
> but
> I don't know where "BPU" is. This can be in a different column each time.
> How can I make a select statement to find out in which column the "BPU"
> value
> is. Something like: select (something) where id='123'
> I want to fit it into a webpage, but I have to get the right select
> statement
> first.
> I pick up the "Charge" column from the page and want to set the first two
> digits from the accompanying column (e.g. label2/usertext2) in the column
> where the "BPU" is.
> This is probably not very clear, but it's a bit difficult to explain.
> So if you need further information to help me, please ask, because I
> really
> need some help here.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>