Posted By

bbrumm on 12/08/16


Tagged

sql Oracle


Versions (?)

Oracle LISTAGG Function with Examples


 / Published in: SQL
 

URL: http://www.databasestar.com/oracle-listagg/

Some examples of the Oracle LISTAGG function, including setting up the tables.

  1. DROP TABLE customers;
  2.  
  3. CREATE TABLE customers (
  4. first_name varchar2(100),
  5. last_name varchar2(100),
  6. country varchar2(20),
  7. full_address CLOB,
  8. employees number,
  9. start_date date
  10. );
  11.  
  12. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('John', 'Smith', 'USA', '10 Long Road', 4, '12-APR-2010');
  13. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Sally', 'Jones', 'USA', '50 Market Street', 10, '04-JUL-2011');
  14. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Steve', 'Brown', 'Canada', '201 Flinders Lane', 15, '21-MAR-2009');
  15. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Mark', 'Allan', 'UK', '8 Smith Street', 23, '1-FEB-2001');
  16. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Adam', 'Cooper', 'USA', '14 Wellington Road', 55, NULL);
  17. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Josh', 'Thompson', NULL, '80 Victoria Street', 1, '10-FEB-2012');
  18. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Peter', 'Manson', 'France', '5 Johnson St', NULL, '16-OCT-2012');
  19. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES (NULL, '', NULL, '155 Long Road', NULL, '16-OCT-2012');
  20.  
  21. SELECT * FROM customers;
  22.  
  23.  
  24. SELECT country, LISTAGG(last_name) WITHIN GROUP (ORDER BY last_name)
  25. FROM customers
  26. GROUP BY country;
  27.  
  28. SELECT LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name)
  29. FROM customers
  30.  
  31.  
  32. SELECT first_name, last_name, country, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) OVER (PARTITION BY country)
  33. FROM customers
  34.  
  35.  
  36. --Examples
  37. SELECT country, last_name
  38. FROM customers;
  39.  
  40.  
  41.  
  42.  
  43. --Example 1
  44. SELECT LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) AS LISTAGG_OUTPUT
  45. FROM customers;
  46.  
  47. --Example 2
  48. SELECT LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) AS LISTAGG_OUTPUT
  49. FROM customers
  50. WHERE country = 'USA';
  51.  
  52. --Example 3
  53. SELECT country, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) AS LISTAGG_OUTPUT
  54. FROM customers
  55. GROUP BY country;
  56.  
  57.  
  58. --Example 4
  59. SELECT country, LISTAGG(last_name, ',') WITHIN GROUP (last_name) AS LISTAGG_OUTPUT
  60. FROM customers
  61. GROUP BY country;
  62.  
  63. --Example 5
  64. SELECT country, LISTAGG(last_name) WITHIN GROUP (ORDER BY last_name) AS LISTAGG_OUTPUT
  65. FROM customers
  66. GROUP BY country;
  67.  
  68. --Example 6
  69. SELECT country, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS LISTAGG_OUTPUT
  70. FROM customers
  71. GROUP BY country;
  72.  
  73. --Example 7
  74. SELECT first_name, last_name, country,
  75. LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) OVER (PARTITION BY country) AS LISTAGG_OUTPUT
  76. FROM customers;
  77.  
  78. --Example 8
  79. SELECT first_name, last_name, country,
  80. LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) OVER (PARTITION BY last_name) AS LISTAGG_OUTPUT
  81. FROM customers;
  82.  
  83. --Example 9
  84. SELECT first_name, last_name, country,
  85. LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) AS LISTAGG_OUTPUT
  86. FROM customers

Report this snippet  

You need to login to post a comment.