DB2 Comma Separated Output by Groups

by

LISTAGG function is new function in DB2 LUW 9.7

see example:

create table myTable (id int, category int);

insert into myTable values (1, 1);
insert into myTable values (2, 2);
insert into myTable values (5, 1);
insert into myTable values (3, 1);
insert into myTable values (4, 2);

example: select without any order in grouped column

select category, LISTAGG(id, ', ') as ids from myTable group by category;

result:

CATEGORY  IDS
--------- -----
1         1, 5, 3
2         2, 4

example: select with order by clause in grouped column

select
  category,
  LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids
from myTable
group by category;

result:

CATEGORY  IDS
--------- -----
1         1, 3, 5
2         2, 4

More Related Contents:

  1. Does DB2 have an “insert or update” statement?
  2. how to transform comma separated column into multiples rows in db2
  3. INNER JOIN in UPDATE sql for DB2
  4. Pivoting in DB2
  5. Regular Expressions in DB2 SQL
  6. How to resolve SQL0418N Error
  7. DB2 Query to retrieve all table names for a given schema
  8. How to check db2 version
  9. How to select just the row in the second table?
  10. how to save sql query result to csv in pandas
  11. Update statement with inner join on Oracle
  12. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  13. MySQL: Alternatives to ORDER BY RAND()
  14. Is there any function in oracle similar to group_concat in mysql? [duplicate]
  15. IN vs ANY operator in PostgreSQL
  16. SQL Case Sensitive String Compare
  17. Exporting data In SQL Server as INSERT INTO
  18. Convert Comma Separated column value to rows
  19. Calculate business days in Oracle SQL(no functions or procedure)
  20. Record returned from function has columns concatenated
  21. Rails 3 execute custom sql query without a model
  22. INSERT rows into multiple tables in a single query, selecting from an involved table
  23. When to Denormalize a Database Design
  24. How to find a table having a specific column in postgresql
  25. Nth max salary in Oracle
  26. divide by zero/null workaround in SSRS 2008 report
  27. What does sp_reset_connection do?
  28. SQL INNER JOIN syntax
  29. SQL query to group by day
  30. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)

Leave a Comment