How to concatenate rows in SQL (I)

Question

I spent more than one day in concatenating the similar rows in SQL.

I am using Squirrel SQL client 3.6 to run the queries. Below is the query i used to get the rows from multiple tables .

select
    A.NACCES as NACCES,
    (CASE WHEN A.CLNKTYP = 1 THEN 'must' WHEN A..CLNKTYP = 2 THEN 'not' ELSE 'NONEED' END ) as Link,
    B.NPART as part
from 
    HPL.KACCST B, HPL.KAMCLT A, HPL.KCACMT C
where 
    A.NMOD = '1212'
    and C.NMOD = A.NMOD
    and C.NSALGNP = '223'
    and C.NCUST = ''
    and C.NACCES = A.NACCES
    and B.NACCES = A.NACCES_LINK

The result is

NACCES     Link   part

  1        must    a
  1        not     b
  1        not     c
  2        must    d
  2        must    e  so on...

Now I need to concatenate part column based on NACCES and Link column..

I have seen several posts on this in Stack overflow and I tried FOR XML PATH query , but no luck.

SELECT
    P.accessory,
    P.Link,
    STUFF((SELECT DISTINCT ',' + S.part 
           FROM (SELECT A.NACCES as NACCES, 
                        (CASE 
                            WHEN A.CLNKTYP = 1 THEN 'must' WHEN A..CLNKTYP = 2 THEN 'not' ELSE 'NONEED' 
                          END) as Link, 
                        B.NPART as part 
                 FROM HPL.KACCST B, HPL.KAMCLT A, HPL.KCACMT C
                 WHERE A.NMOD = '1212' AND C.NMOD = A.NMOD AND C.NSALGNP = '223'
                   AND C.NCUST = '' AND C.NACCES = A.NACCES AND B.NACCES = A.NACCES_LINK) AS S 
          WHERE S.NACCES = P.NACCES  
            AND S.Link = P.Link 
          FOR XML PATH('')), 1, 1, '') AS PART
FROM
    (SELECT
         A.NACCES as NACCES,
         (CASE WHEN A.CLNKTYP = 1 THEN 'must' WHEN A..CLNKTYP = 2 THEN 'not' ELSE 'NONEED' END ) as Link,
         B.NPART as part
     FROM 
         HPL.KACCST B, HPL.KAMCLT A, HPL.KCACMT C
     WHERE
         A.NMOD = '1212'
         AND C.NMOD = A.NMOD
         AND C.NSALGNP = '223'
         AND C.NCUST = ''
         AND C.NACCES = A.NACCES
         AND B.NACCES = A.NACCES_LINK) AS P
GROUP BY 
    P.NACCES, P.Link

ERROR:

DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=FOR;;( . AT

MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE, DRIVER=3.53.95
SQLState: 42601

ErrorCode: -199
Error: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.53.95
SQLState: 26501
ErrorCode: -514

I tried with GROUP_CONCAT also in Squirrel tool, I am getting GROUP_CONCAT is not available error.

Please make a note that i am using Squirrel to run the queries and I hope this causes FOR XML syntax not to be executed .

I tried with COALESCE , but this also didn't work. Please help me out.

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Sep 04 '16 at 07:24
Sure i will avoid those old-style joins. But now ,that's not my concern . Can you help me in concatenating the rows @marc_s — pravallika, Sep 04 '16 at 07:33
DB2 has `listagg()`: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0058709.html — , Sep 04 '16 at 10:08
https://stackoverflow.com/questions/23359019/db2-aggregate-function/23364019#23364019 or http://stackoverflow.com/q/32617670/330315 — , Sep 04 '16 at 10:09
"Now I need to concatenate part column based on NACCES and Link column.." is nowhere near clear enough to explain the problem/your intent. — Erwin Smout, Sep 04 '16 at 18:01
You told us what client (which is mostly irrelevant) but nothing about what version of DB2 nor the platform it runs on. Can you edit your question to include necessary environment info? — user2338816, Sep 05 '16 at 00:12

score 0 · Answer 1 · answered Sep 09 '16 at 06:47

After lot of searching i found the query for concatenating the columns in db2.

SELECT NACCES,LINK,replace(replace(replace(cast(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "x", S.PART) order by S.PART) AS CLOB(1000)) as varchar(1024)), '', ',') , '', '') , '', '') as PART FROM ( select accessorym1_.NACCES as NACCES, (CASE WHEN accessorym1_.CLNKTYP = 1 THEN 'must' WHEN accessorym1_.CLNKTYP = 2 THEN 'notcompatible' ELSE 'NONEED' END ) as LINK, accessory0_.NPART as PART from HPL.KACCST accessory0_, HPL.KAMCLT accessorym1_, HPL.KCACMT customerac2_ where accessorym1_.NMOD='1212' and customerac2_.NMOD=accessorym1_.NMOD and customerac2_.NSALGNP='223' and customerac2_.NCUST='' and customerac2_.NACCES=accessorym1_.NACCES and accessory0_.NACCES=accessorym1_.NACCES_LINK ) P

Thank you guys for your comments.

How to concatenate rows in SQL (I)

1 Answers1