sql server - SQL: Avoid duplicate data -
table1:
person_id name salary_revisions 1 test1 100 1 test1 200 2 test2 300 2 test2 400 table2:
person id department -------------------------- ---------------- 1 physics 1 chemistry 2 maths i result like:
person_id name salary_revisions department --------------------- ------------------ ---------------------- -------------- 1 test1 100 physics 1 test1 200 chemistry 2 test2 300 maths 2 test2 400 actual:
person id name salary revisions department ------------------ --------- --------------------- ---------------- 1 test1 100 physics 1 test1 200 physics 1 test1 100 chemistry 1 test1 200 chemistry 2 test2 300 maths 2 test2 400 maths could please me implement expected result?
while implementing wrote stored procedure left joining table 1 table 2 using person id. executing query in database returns actual result.
sql query:
select table1.person_id, table1.name, table1.salary_revisions, table2.department table1 left outer join table2 on table1.person_id=table2.person_id
i can't quite understand usage scenario, think want.
two cte's unique values each column per person, , full outer join combine them row row.
with salary_revision ( select person_id, name, salary_revisions sr, row_number() on (partition person_id order person_id) row table1 ), department ( select person_id, department, row_number() on (partition person_id order person_id) row table2 ) select sr.person_id,sr.name, sr.sr, d.department salary_revision sr full outer join department d on sr.person_id = d.person_id , sr.row = d.row
Comments
Post a Comment