Select and merge multiple columns in DB2-CodePudding

here is my table:

ID	SEQ	DATE_FROM	DATE_TO	NOTES
ELEM1	1	2022-04-01	2023-03-31	First part
ELEM1	2	2022-04-01	2023-03-31	Second part
ELEM1	3	2022-04-01	2023-03-31	Third part
ELEM2	1	2018-11-12	2099-12-31	First part
ELEM2	2	2018-11-12	2099-12-31	Second part
ELEM2	3	2018-11-12	2099-12-31	Third part
ELEM2	4	2018-11-12	2099-12-31	Forth part

I'd like to return each ID by merging and concatenating SEQ and NOTES, so that I select only ID, DATE_FROM, DATE_TO, NOTES like that:

ID	DATE_FROM	DATE_TO	NOTES
ELEM1	2022-04-01	2023-03-31	First part Second part Third part
ELEM2	2018-11-12	2099-12-31	First part Second part Third part Forth part

Then, I'll insert that result to another table!

I tried to use GROUP BY, but I'm not good at SQL queries and I don't know how to use it:

SELECT ID, DATE_FROM, DATE_TO, NOTES
FROM MYTABLE
GROUP BY ID, NOTES

Thanks

CodePudding user response：

If LISTAGG is not available maybe recursive CTE are. The following query relies on

if SEQ > 1 exists then SEQ - 1 also exists
SEQ is unique

query:

with MYTABLE (ID, SEQ, DATE_FROM, DATE_TO, NOTES) as (
  values
  ('ELEM1', 1, date '2022-04-01', date '2023-03-31', 'First part'),
  ('ELEM1', 2, date '2022-04-01', date '2023-03-31', 'Second part'),
  ('ELEM1', 3, date '2022-04-01', date '2023-03-31', 'Third part'),
  ('ELEM2', 1, date '2018-11-12', date '2099-12-31', 'First part'),
  ('ELEM2', 2, date '2018-11-12', date '2099-12-31', 'Second part'),
  ('ELEM2', 3, date '2018-11-12', date '2099-12-31', 'Third part'),
  ('ELEM2', 4, date '2018-11-12', date '2099-12-31', 'Forth part')
),
allnotes (ID, seq, ALLNOTES) as (
  select ID, seq, varchar(notes, 4000) from MYTABLE where seq = 1
  union all
  select mytable.id, mytable.seq, allnotes.allnotes || ' ' || mytable.notes from allnotes, mytable where (mytable.id, mytable.seq) = (allnotes.id, allnotes.seq   1)
),
groups as (
  select id, min(date_from) date_from, max(date_to) date_to, max(seq) seq from mytable group by id
)
select groups.id, date_from, date_to, allnotes from groups inner join allnotes on (allnotes.id, allnotes.seq) = (groups.id, groups.seq)

DB<>Fiddle

CodePudding user response：

AS it was already written in comments you need to use LISTAGG FUNCTION. I am not sure how did you used it but it should work for you.

Here is the demo

And here is the code from that demo:

select id, min(dat_from), max(dat_to), listagg(notes, ',')
from test
group by id

Watch out which columns you use in group by. You can not group by column Notes and use it in listagg function.

If your title is correct "Select and merge multiple columns in DB2" then you can concatenate two columns like this:

select id, min(dat_from), max(dat_to), listagg(notes|| ' ' || id, ',')
from test
group by id

But you have shown us your desired result where we can assume you do not want this...

CodePudding user response：

SQLCODE = -4700:

Explanation

An attempt was made to use a new capability before the required function level is activated. Capabilities that are introduced in a Db2 function level cannot be used before that function level is activated.

Seems, that you use Db2 for Z/OS 12 (and not Db2 for LUW 10.5.0.9 as you mentioned), where LISTAGG is available with FL501 which is not activated in your system.

Try XMLAGG as alternative. This should work as is, if you uncomment the commented out block.

/*
WITH MYTABLE (ID, SEQ, DATE_FROM, DATE_TO, NOTES) AS
(
          SELECT 'ELEM1', 1, '2022-04-01', '2023-03-31', 'First part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM1', 2, '2022-04-01', '2023-03-31', 'Second part' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM1', 3, '2022-04-01', '2023-03-31', 'Third part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 1, '2018-11-12', '2099-12-31', 'First part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 2, '2018-11-12', '2099-12-31', 'Second part' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 3, '2018-11-12', '2099-12-31', 'Third part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 4, '2018-11-12', '2099-12-31', 'Forth part'  FROM SYSIBM.SYSDUMMY1
)
*/
SELECT 
  ID
, MIN (DATE_FROM) AS DATE_FROM
, MAX (DATE_TO)   AS DATE_TO
, SUBSTR (XMLSERIALIZE (XMLAGG (XMLTEXT (' ' || NOTES) ORDER BY SEQ) AS VARCHAR (1000)), 2) AS NOTES
FROM MYTABLE
GROUP BY ID