Combine values from different records using comma

apeksha.wagh · #1 (**permalink**) 07-18-11, 04:44

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) ??

tonkuma · #2 (**permalink**) 07-18-11, 05:30

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.

tonkuma · #3 (**permalink**) 07-18-11, 08:50

Quote:

... 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.

Quote:

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.

tonkuma · #4 (**permalink**) 07-18-11, 16:09

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.

apeksha.wagh · #5 (**permalink**) 07-20-11, 04:19

Hey tonkuma,

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

Internet Services

Web Development

Digital Marketing

Consumer Tech