Multiple rows into one field? 1

[twifosp]

Lets say I have a table that goes

id desc
1 abc
1 def
1 ghi
2 jkl
2 mno
2 pqr
3 stu
3 vwx
3 yz

how can I write a select statement that results in

1 abcdefghi
2 jklmnopqr
3 stuvwxyz

 

[r937]

here's one way:

select a.id
     , a.desc + b.desc + c.desc
  from yourtable a
     , yourtable b
     , yourtable c
 where a.id = b.id
   and b.id = c.id
   and a.desc < b.desc
   and b.desc < c.desc
order by 1

rudy

http://r937.com/

SQL Consulting

 

[twifosp]

Ok, lets say I have about 14,000 of these incidents at around 100,000 rows

I can't do that many joins

 

[r937]

you shoulda said that the first time

i saw 3 ids with 3 descs each

i think you will have to scan the table, order by id and desc, and then do the concat in a loop





rudy

http://r937.com/

SQL Consulting

 

[twifosp]

Thanks for the help.

Concat in a loop? Can you elaborate?

 

[r937]

run the query

set lastid = ''
set str = ''

loop:
if id <> lastid
{ print id,str
set lastid=id
set str = desc
}
else str = str||desc
end loop

print id,str






rudy

http://r937.com/

SQL Consulting

 

[twifosp]

I apologize, are we using mssql here?

How are you defining a lastid when it's going to be the same (without a pk) to the regular id?

I apologize if I'm missing something obvious.

 

[r937]

no, man, that was pseudocode

lastid is a scripting variable



rudy

http://r937.com/

SQL Consulting

 

[twifosp]

ok, well that's fine, but I still don't see how you would even set lastid = a distinct value and expect it to work.

What critera would you use to set it to a distinct value, and then that as an update? It doesn't make sense, if you specify a where after that it will pull ALL those ID's that you have lastid set to. You can't set lastid to 1 and expect it to be different than id when it, as well is 1.

 

[cheyney]

Create a udf to get your concatenated results:

CREATE FUNCTION dbo.udf_ConcatRows
(
@input int
)
RETURNS varchar(8000) AS
BEGIN

DECLARE @Output varchar(8000)
SELECT @Output = COALESCE(@Output, '') + Cast(desc as varchar(200))
FROM tblTable
WHERE [ID] = @input

RETURN @Output
END


then run your query

select id, dbo.udf_ConcatRows(id)
from tblTable
group by id


Cheyney

 

[twifosp]

Cheyney,

Perfect! Exactly what I was looking for but couldn't figure out. I'm new to functions, but I have the feeling this will not be the last time I use one

Thanks!
Mike

 

[adventurous1]

I am a SQL newbie and have this same problem but we are using Oracle 9i. Can anyone provide a solution for this in SQL or PL/SQL?

Any help offered is appreciated.

adventurous1@ureach.com

 

[DaOtH]

Nice function,
But anybody any idea how to make it returning only unique values ?
Can't figure it out

&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[vongrunt]

Instead of

FROM tblTable
WHERE [ID] = @input


... use:

FROM
( select distinct desc
from tblTable
where [ID] = @input
) someAlias

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DaOtH]

stupid... should have seen that myself

Tnxs vongrunt

&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963