1

When I run the following SQL query in my Oracle database:

SELECT p.pkt_nazwa, 
       u.us_nazwa 
FROM   punkty p, 
       kategorie_uslug ku, 
       usluga u 
WHERE  ku.pkt_id = p.pktk_1_id 
       AND ku.us_id = u.usk_1_id 
ORDER  BY p.pkt_nazwa;

I get the following output:

NAME                | SERVICE
--------------------|-------------------
Baita De Mario      | WC 
Baita De Mario      | Kuchnia
Baita De Mario      | Nocleg
Bistro-Cafe         | Bar
Bistro-Cafe         | Dyskoteka
Bistro-Cafe         | Kuchnia

How would I go about to get the following output?

NAME                | SERVICES
--------------------|-------------------
Baita De Mario      | WC, Kuchnia, Nocleg 
Bistro-Cafe         | Bar, Dyskoteka, Kuchnia
makbol
  • 310
  • 2
  • 12
  • @JNK The original question was very poorly written and OP did very little research. However, I thought it was a very good question (it's a common question in SQL Server and MySQL but never saw it for Oracle) so I edited it to look like a decent question. – Adriano Carneiro Nov 22 '11 at 19:15
  • @Adrian - thanks for putting in the effort – JNK Nov 22 '11 at 19:43

3 Answers3

3
SELECT p.pkt_nazwa AS name, wm_concat(u.us_nazwa) AS services
FROM   punkty p
JOIN   kategorie_uslug ku ON ku.pkt_id = p.pktk_1_id
JOIN   usluga u ON u.usk_1_id = ku.us_id
GROUP  BY p.pkt_nazwa
ORDER  BY p.pkt_nazwa;

wm_concat() function is present in most versions.
More about string aggregating in Oracle.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

Here's an article explaining some approaches to what you're trying to do in Oracle:

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Note that if you're running 11g Release 2 then LISTAGG is the way to go.

Shaun
  • 2,446
  • 19
  • 33
0

Since Oracle 11.2 this has become easier using listagg()

SELECT p.pkt_nazwa AS name, 
       listagg(u.us_nazwa) WITHIN GROUP (ORDER BY u.us_nazwa) AS services
FROM punkty p
  JOIN kategorie_uslug ku ON ku.pkt_id = p.pktk_1_id
  JOIN usluga u ON u.usk_1_id = ku.us_id
GROUP BY p.pkt_nazwa
ORDER BY p.pkt_nazwa;