Concanate to one row

[Pekka]

Ok, this is not so relevant question. I'm just wonder if this is possible by db2 sql syntax. Let's assume I have a table as follows:
Name Hobby
Jim Flowers
Jane Karate
Jane Shooting
Jim Gardening

and if I want the result looks like:

Name Hobbies
Jim Flowers, Gardening
Jane Karate, Shooting

I've sometimes seen this done by Ms Access, but they might have used some user definied function.

 

[MarcLodge]

Hi Pekka,
As far as I am aware, you can't do this in SQL. See thread220-506163 for a bit more info.

I think that you are going to need a tool that can format the report for you (like Access or QMF) or you are going to have to program it youself.

Marc

 

[cotaurus]

The closest approach:
db2 &quot;select a.name||' '||a.hobby||', '||b.hobby from table a, table b where a.name=b.name and a.hobby<b.hobby&quot;

 

[PruSQLer]

As long as you don't have too many different types of hobbies, you can do it although it's not as clean as a real programming method.


select name
, max(case when hobby = 'Flowers' then hobby end) as hobby1
, max(case when hobby = 'Gardening' then hobby end) as hobby2
, max(case when hobby = 'Karate' then hobby end) as hobby3
, max(case when hobby = 'Shooting' then hobby end) as hobby4
, max(case when hobby = 'Boating' then hobby end) as hobby5
etc, etc, til the end of the hobbies
FROM table 1
Group by name
Order by name