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

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.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 SQL a Little Big Problem

    ‏2013-03-27T23:35:02Z  
    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

    Re: DB2 SQL a Little Big Problem

    ‏2013-03-27T23:48:50Z  
    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?
    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

    Re: DB2 SQL a Little Big Problem

    ‏2013-03-28T13:01:00Z  
    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?
    I'm looking for a single VARCHAR concatenated CODCOMP field. Thanks
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 SQL a Little Big Problem

    ‏2013-03-28T13:16:11Z  
    I'm looking for a single VARCHAR concatenated CODCOMP field. Thanks
    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