0

So we have PIVOT keyword in Oracle /SQL Serverwhen you convert rows to columns. Not in DB2, I want to convert row results and concatenate them into a column, dynamically, as in, I do not know the number of rows I might get, they might vary. Eg table x

COL1  COL2
ABC  10
ABC  20
ABC  30

I want to display this as

COL1  COL2
ABC   10,20,30

But the count of records might vary so I cannot use case.

Want to do this for queries of many tables not a particular query; a function or most preferably in the select query itself.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Shama Thakur
  • 1
  • 5
  • Which DB2 "brand" exactly? LUW? iSeries? Z/OS? DB2 LUW supports listagg(): https://stackoverflow.com/questions/7188542/ –  Nov 29 '18 at 07:00
  • Hi, Yes db2 LUW. Yes, I did use LISTAGG() and it works, however after concatenation, after a certain limit it gives me size error. Any workaround for this? using CAST is an option eg: LISTAGG(CAST( AS VARGRAPHIC(10000)), ', ') but still it reserves a fixed length. – Shama Thakur Nov 29 '18 at 08:11
  • LISTAGG is limited to the max VARCHAR length, i.e. 32000 bytes or so – Paul Vernon Nov 29 '18 at 08:42
  • Thanks for the reply. I see, is there anyway this can be dynamic, because I will be reserving 32000 necessarily, and what if the string increases 32000? Any idea for a workaround for this? – Shama Thakur Nov 29 '18 at 08:46

2 Answers2

0

If you are using Db2 LUW 11.1.4.4 release, you might be able to workaround using sysibm.json_array function: 

VALUES JSON_ARRAY(SELECT DEPTNO FROM DEPT);

1
-------------------------------
["F22","G22","H22","I22","J22"]

you can then stored the json_array in a character based column, varchar, clob.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Kevin See - Db2 Hybrid Cloud
  • 76
  • 1
0

For longer strings.

select substr(xmlserialize(
xmlquery('$L/text()' passing XMLAGG(xmlelement(name "a", ','||colname)) as "L")
as clob(1m)
), 2)
from syscat.columns;
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16