Oracle - Display in Single Row

Displaying multiple rows in Single row
===============================

[oracle@testing scripts]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 02:05:54 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn aj/aj
Connected.

-- Create a sample table

SQL> create table AJ.emp (ename varchar2(20));

Table created.

-- insert some sample rows

INSERT INTO AJ.emp
  VALUES ('one');
INSERT INTO AJ.emp
  VALUES ('two');
INSERT INTO AJ.emp
  VALUES ('three');
COMMIT;

-- list the output

SQL> select ename from emp;

ENAME
--------------------
one
two
three

-- Now our aim is to display the result above in one single row instead of 3 rows

1st way: There is a function wm_concat in wmsys schema which serves the purpose

SQL> select wmsys.wm_concat(ename) empname from emp;

EMPNAME
----------------------------------
one,two,three

2nd way: Using LISTAGG function

select LISTAGG(ename, ',') within group (order by ename) ename from emp;

ENAME
----------------------------------
one,three,two

-- Another example

-- table which will store country and language spoken

create table country (cname varchar2(30), language varchar2(30));

-- some sample data

INSERT INTO AJ.country
  VALUES ('Algeria', 'Arabic');
INSERT INTO AJ.country
  VALUES ('Argentina', 'Spanish');
INSERT INTO AJ.country
  VALUES ('Argentina', 'English');
INSERT INTO AJ.country
  VALUES ('Brazil', 'Portuguese');
INSERT INTO AJ.country
  VALUES ('Brazil', 'Spanish');
INSERT INTO AJ.country
  VALUES ('Brazil', 'English');
INSERT INTO AJ.country
  VALUES ('India', 'Hindi');
INSERT INTO AJ.country
  VALUES ('India', 'English');
INSERT INTO AJ.country
  VALUES ('India', 'Bengali');
INSERT INTO AJ.country
  VALUES ('India', 'Kannada');
INSERT INTO AJ.country
  VALUES ('India', 'Sanskrit');
INSERT INTO AJ.country
  VALUES ('India', 'Marathi');
INSERT INTO AJ.country
  VALUES ('United Kingdom', 'English');
INSERT INTO AJ.country
  VALUES ('United Kingdom', 'Welsh');
INSERT INTO AJ.country
  VALUES ('United States', 'English');
INSERT INTO AJ.country
  VALUES ('United States', 'Spanish');
COMMIT;

-- display output

SQL> select cname "Country", language "Language Spoken" from AJ.country;

Country                        Language Spoken
------------------------------ ------------------------------
Algeria                        Arabic
Argentina                      Spanish
Argentina                      English
Brazil                         Portuguese
Brazil                         Spanish
Brazil                         English
India                          Hindi
India                          English
India                          Bengali
India                          Kannada
India                          Sanskrit
India                          Marathi
United Kingdom                 English
United Kingdom                 Welsh
United States                  English
United States                  Spanish

-- The output above is definitely not serving our purpose as we want country name with list of languages spoken there.
Now we will try to use WMSYS.WM_CONCAT and LISTAGG to get our required result.

-- Using LISTAGG

set lines 200
col "Language Spoken" for a60
col "Country" for a20
SELECT 
cname "Country", 
LISTAGG(language, ',') WITHIN GROUP (ORDER BY cname) AS "Language Spoken"
FROM   AJ.country
GROUP BY cname;

Country              Language Spoken
-------------------- ------------------------------------------------------------
Algeria              Arabic
Argentina            English,Spanish
Brazil               English,Portuguese,Spanish
India                Bengali,English,Hindi,Kannada,Marathi,Sanskrit
United Kingdom       English,Welsh
United States        English,Spanish

6 rows selected.

-- Using WMSYS.WM_CONCAT

SELECT
  cname "Country",
  wmsys.wm_concat(language) "Language Spoken"
FROM AJ.country
GROUP BY cname;

Country              Language Spoken
-------------------- ------------------------------------------------------------
Algeria              Arabic
Argentina            Spanish,English
Brazil               Portuguese,English,Spanish
India                Hindi,Marathi,Sanskrit,Kannada,Bengali,English
United Kingdom       English,Welsh
United States        English,Spanish

6 rows selected.

Definitely we can write our own pl/sql functions to achieve the same.