Topic
4 replies Latest Post - ‏2013-03-28T13:16:11Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic DB2 SQL a Little Big Problem

‏2013-03-25T16:10:11Z |
Hello DB2 Expert,

Here a little big problem

Table to contain source data
CODFORN CODCOMP
1000 A1
1000 A2
1000 A3
500 S1
500 S2
...
...

The Problem
Expected Result set
1000 A1;A2;A3
500 S1;S2

There are not fixed values about the number of same CODFORN number.
Best Regards.
Updated on 2013-03-28T13:16:11Z at 2013-03-28T13:16:11Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: DB2 SQL a Little Big Problem

    ‏2013-03-27T23:35:02Z  in response to SystemAdmin
    What you are describing is possibly a PIVOT table. (https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/pivoting_tables56?lang=en)

    Are you looking at getting a result set with multiple columns representing CODCOMP, or a single VARCHAR concatenated CODCOMP field?
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: DB2 SQL a Little Big Problem

      ‏2013-03-27T23:48:50Z  in response to SystemAdmin
      Also, a little later in the forum, someone has already answered a related question:

      https://www.ibm.com/developerworks/forums/thread.jspa?threadID=479580&tstart=0

      :-)
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: DB2 SQL a Little Big Problem

      ‏2013-03-28T13:01:00Z  in response to SystemAdmin
      I'm looking for a single VARCHAR concatenated CODCOMP field. Thanks
      • SystemAdmin
        SystemAdmin
        17917 Posts
        ACCEPTED ANSWER

        Re: DB2 SQL a Little Big Problem

        ‏2013-03-28T13:16:11Z  in response to SystemAdmin
        Here the solution

        SELECT
        codforn,
        SUBSTR(XMLCAST(XMLGROUP(';' || codcomp AS a
        ORDER BY codcomp)
        AS VARCHAR(60)), 2) AS codcomp
        FROM db2cm0.tforcom
        GROUP BY codforn;
        thanks a lot for your support