Join Data from 4 tables in DB2 and add a new column that identifies, which table the record is from

Question

I have four tables

Pay, Location, deparment, job

each one of them has 3 colums EmpId, Code, Date.

some records in each table are unique to that table, whereas some records exists in multiple tables.

i want all the data together in one table without duplicates and also there should be a new column having name of the tables that the record exists in

So our final data will be something like

#Sr.no------------EmpID----------Code------Date----------Tables in which the record exists
1.--------------- E001 --------- C1 ------ 1 Feb 2014 ---------------- Pay, Department
2.--------------- E002 --------- C2 ------ 2 Jan 2014 ---------------- Location 
3.--------------- E003 --------- C3 ------ 3 Mar 2014 ---------------- Job
4.--------------- E004 --------- C4 ------ 4 Jan 2014 ---------------- Location, Pay 
5.--------------- E005 --------- C5 ------ 6 Mar 2014 ---------------- Dept, Job 
6.--------------- E006 --------- C6 ------ 3 Feb 2014 ---------------- Pay, Job, Location 
7.--------------- E007 --------- C7 ------ 2 Aug 2014 ---------------- Pay, Dept, Job, Loc 
8.--------------- E008 --------- C8 ----- 19 sep 2014 ---------------- Department 
9.--------------- E009 --------- C9 ----- 22 dec 2014 ---------------- Pay, Dept, Job, Loc

score 2 · Accepted Answer · edited May 23 '17 at 10:33

Use Union all to combine the results of different tables and use a additional column to differentiate the rows.

After that use ListAgg function to group the duplicate rows into comma separated values.Try this.

SELECT EmpId,Code,Date,Listagg(Tablename,',') within GROUP (ORDER BY empid) AS Tablename 
FROM   (SELECT EmpId,Code,Date,'Pay' AS Tablename
        FROM   Pay
        UNION ALL
        SELECT EmpId,Code,Date,'Location' AS Tablename
        FROM   Location
        UNION ALL
        SELECT EmpId,Code,Date,'deparment' AS Tablename
        FROM   deparment
        UNION ALL
        SELECT EmpId,Code,Date,'job' AS Tablename
        FROM   job) A
GROUP  BY EmpId,Code,Date

ListAgg Function referred from this answer

Join Data from 4 tables in DB2 and add a new column that identifies, which table the record is from

1 Answers1