Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Combine values from different records using comma

    Hi,
    I am using Db2 as my database. Following are the 4 tables. I need to concatenate records from 3 tables and display them using comma. Kindly help me how to achieve this.


    ------------------------------------------------------------------------------------------------------------
    CREATE TABLE CANDIDATE_SKILLS_INFO (
    CANDIDATE_ID INTEGER NOT NULL ,
    CANDIDATE_SPECIALISATION_ID INTEGER NOT NULL ,
    EXPERIENCE_YEARS INTEGER NOT NULL ,
    LAST_USED DATE ,
    CANDIDATE_SKILLSET_INFO_ID INTEGER NOT NULL)



    ALTER TABLE CANDIDATE_SKILLS_INFO
    ADD PRIMARY KEY
    (CANDIDATE_ID,
    CANDIDATE_SPECIALISATION_ID);

    INSERT INTO CANDIDATE_SKILLS_INFO( CANDIDATE_ID,CANDIDATE_SPECIALISATION_ID,EXPERIENC E_YEARS,LAST_USED) VALUES (1,1, 10, '10-10-2001');

    INSERT INTO CANDIDATE_SKILLS_INFO( CANDIDATE_ID,CANDIDATE_SPECIALISATION_ID,EXPERIENC E_YEARS,LAST_USED) VALUES (1,2, 5, '10-10-2001');

    INSERT INTO CANDIDATE_SKILLS_INFO( CANDIDATE_ID,CANDIDATE_SPECIALISATION_ID,EXPERIENC E_YEARS,LAST_USED) VALUES (1,3, 15, '10-10-2001');


    ------------------------------------------------------------------------------------------------------------

    CREATE TABLE SKILL_SPECIAL_MASTER (
    SKILL_SPECIAL_ID INTEGER NOT NULL ,
    SPECIALISATION VARCHAR(256) NOT NULL ,
    SKILL_BASIC2_ID INTEGER NOT NULL )

    ALTER TABLE SKILL_SPECIAL_MASTER
    ADD PRIMARY KEY
    (SKILL_SPECIAL_ID);

    INSERT INTO SKILL_SPECIAL_MASTER values(1,'JAVA',1);
    INSERT INTO SKILL_SPECIAL_MASTER values(2,C,1);
    INSERT INTO SKILL_SPECIAL_MASTER values(3,'MOUSE',3);


    ------------------------------------------------------------------------------------------------------------


    CREATE TABLE SKILL_MASTER_BASIC2 (
    SKILL_BASIC2_ID INTEGER NOT NULL ,
    BASIC_SKILL2 VARCHAR(256) NOT NULL ,
    SKILL_BASIC1_ID INTEGER NOT NULL )
    ALTER TABLE SKILL_MASTER_BASIC2
    ADD PRIMARY KEY
    (SKILL_BASIC2_ID);

    INSERT INTO SKILL_MASTER_BASIC2 VALUES(1, 'DEVLOPMENT', 1);
    INSERT INTO SKILL_MASTER_BASIC2 VALUES(2, 'TESTING', 1);
    INSERT INTO SKILL_MASTER_BASIC2 VALUES(3, 'CATG1', 2);


    ------------------------------------------------------------------------------------------------------------

    CREATE TABLE SKILL_MASTER_BASIC1 (
    SKILL_BASIC1_ID INTEGER NOT NULL ,
    BASIC_SKILL1 VARCHAR(256) NOT NULL )


    ALTER TABLE SKILL_MASTER_BASIC1
    ADD PRIMARY KEY
    (SKILL_BASIC1_ID);


    INSERT INTO SKILL_MASTER_BASIC1 values(1,'SOFTWARE');
    INSERT INTO SKILL_MASTER_BASIC1 values(2,'HARDWARE');




    I need to display table in the front (using java/jsp) with following columns and result

    Candidate Id Specialization Basic Skills2 Basic Skills 1
    1 Java, C, Mouse Development, Catg1 Software, Hardware


    How can I achive this (i.e separate rows with comma and have only distinct values in all 3 columns of the table) ??

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using DB2 9.7 fixpack 4 for LUW,
    you may want to use LISTAGG function.

    Otherwise, you can use XMLGROUP or XMLAGG function instead of LISTAGG function.

    Another way would be recursive-common-table-expression.

    You would find some examples by searching this forum with those keywords.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... and have only distinct values in all 3 columns of the table
    Sorry, I forgot this part.
    Simple use of LISTAGG function may repeat duplicated values.

    I thought that specializations would not be duplicated for a candidate_id, because of this.
    ALTER TABLE CANDIDATE_SKILLS_INFO
    ADD PRIMARY KEY
    (CANDIDATE_ID,
    CANDIDATE_SPECIALISATION_ID);
    Here is an example removing duplications in basic_skills2 and basic_skills1:
    Code:
    SELECT Candidate_Id
         , VARCHAR(
              LISTAGG( Specialisation , ', ' )
            , 20 ) AS Specialisations
         , VARCHAR(
              LISTAGG( CASE rnum_bs2
                       WHEN rank_bs2 THEN
                            Basic_Skill2
                       END , ', ' )
            , 20 ) AS Basic_Skills2
         , VARCHAR(
              LISTAGG( CASE rnum_bs1
                       WHEN rank_bs1 THEN
                            Basic_Skill1
                       END , ', ' )
            , 20 ) AS Basic_Skills1
     FROM  (SELECT csi.Candidate_Id
                 , ssm.Specialisation
                 , smb2.Basic_Skill2
                 , ROW_NUMBER() OVER(ORDER BY smb2.Basic_Skill2) rnum_bs2
                 , RANK()       OVER(ORDER BY smb2.Basic_Skill2) rank_bs2
                 , smb1.Basic_Skill1
                 , ROW_NUMBER() OVER(ORDER BY smb1.Basic_Skill1) rnum_bs1
                 , RANK()       OVER(ORDER BY smb1.Basic_Skill1) rank_bs1
             FROM  CANDIDATE_SKILLS_INFO csi
             INNER JOIN
                   SKILL_SPECIAL_MASTER  ssm
               ON  ssm.SKILL_SPECIAL_ID = csi.CANDIDATE_SPECIALISATION_ID
             INNER JOIN
                   SKILL_MASTER_BASIC2   smb2
               ON  smb2.SKILL_BASIC2_ID = ssm.SKILL_BASIC2_ID
             INNER JOIN
                   SKILL_MASTER_BASIC1   smb1
               ON  smb1.SKILL_BASIC1_ID = smb2.SKILL_BASIC1_ID
           ) s
     GROUP BY
           Candidate_Id
    ;
    ------------------------------------------------------------------------------
    
    CANDIDATE_ID SPECIALISATIONS      BASIC_SKILLS2        BASIC_SKILLS1       
    ------------ -------------------- -------------------- --------------------
               1 MOUSE, JAVA, C       CATG1, DEVLOPMENT    HARDWARE, SOFTWARE  
    
      1 record(s) selected.
    Last edited by tonkuma; 07-18-11 at 17:13. Reason: Replace "Specialization" to "Specialisation"

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example specified order of items in each list(result column).

    Example 2:
    Code:
    SELECT candidate_id
         , VARCHAR(
              LISTAGG( specialisation , ', ' )
                  WITHIN GROUP(ORDER BY special_id)
            , 20 ) AS specialisations
         , VARCHAR(
              LISTAGG( basic_skill2 , ', ' )
                  WITHIN GROUP(ORDER BY special_id)
            , 20 ) AS basic_skills2
         , VARCHAR(
              LISTAGG( basic_skill1 , ', ' )
                  WITHIN GROUP(ORDER BY special_id)
            , 20 ) AS basic_skills1
     FROM  (SELECT csi.candidate_id
                 , csi.candidate_specialisation_id AS special_id
                 , ssm.specialisation
                 , NULLIF(
                      sm2.basic_skill2
                    , LAG(sm2.basic_skill2) OVER(ORDER BY sm2.basic_skill2)
                   ) basic_skill2
                 , NULLIF(
                      sm1.basic_skill1
                    , LAG(sm1.basic_skill1) OVER(ORDER BY sm1.basic_skill1)
                   ) basic_skill1
             FROM  candidate_skills_info csi
             INNER JOIN
                   skill_special_master  ssm
               ON  ssm.skill_special_id = csi.candidate_specialisation_id
             INNER JOIN
                   skill_master_basic2   sm2
               ON  sm2.skill_basic2_id  = ssm.skill_basic2_id
             INNER JOIN
                   skill_master_basic1   sm1
               ON  sm1.skill_basic1_id  = sm2.skill_basic1_id
           ) s
     GROUP BY
           candidate_id
    ;
    ------------------------------------------------------------------------------
    
    CANDIDATE_ID SPECIALISATIONS      BASIC_SKILLS2        BASIC_SKILLS1       
    ------------ -------------------- -------------------- --------------------
               1 JAVA, C, MOUSE       DEVLOPMENT, CATG1    SOFTWARE, HARDWARE  
    
      1 record(s) selected.
    Last edited by tonkuma; 07-18-11 at 17:18.

  5. #5
    Join Date
    Jul 2011
    Posts
    2
    Hey tonkuma,

    you are grt. Thanks for the solution. It worked perfectly for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •