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