CODEWITHSUNDEEP
sqloracle-databaseplsql
Nipun Alahakoon
Nipun Alahakoon

Reputation: 2862

Can anyone explain this Query?

with a as (
select a.*, row_number() over (partition by department order by attributeID) rn
  from attributes a),
e as (
select employeeId, department, attribute1, 1 rn from employees union all
select employeeId, department, attribute2, 2 rn from employees union all
select employeeId, department, attribute3, 3 rn from employees
)
select e.employeeId, a.attributeid, e.department, a.attribute, a.meaning, 
   e.attribute1 as value 
 from e join a on a.department=e.department and a.rn=e.rn 
order by e.employeeId, a.attributeid

this query is written by Ponder Stibbons for the answer of this question. But i am too dizzy with it as i quite don't understand what is going on here. i am new to SQL . so i would appreciate if anyone can explain what is happening on this query . thank you

Upvotes: 0

Views: 84

Answers (1)

SQLChao
SQLChao

Reputation: 7847

Basically he unpivots the data using 3 select statements (1 for each attribute) and UNION them together to make a common table expression so that he gets rows for each employees attribute. 

select employeeId, department, attribute1, 1 rn from employees union all
select employeeId, department, attribute2, 2 rn from employees union all
select employeeId, department, attribute3, 3 rn from employees

The other table he using a window function to assign a number to attribute, department. He uses this number later to join back to his unpivoted data. He posted his code for the example. 

select a.*, row_number() over (partition by department order by attributeID) rn
  from attributes a

I would suggest you use his example data he provided and run the following. This will show you the CTEs. I think once you see that data it will make more sense. 

with a as (
select a.*, row_number() over (partition by department order by attributeID) rn
  from attributes a),
e as (
select employeeId, department, attribute1, 1 rn from employees union all
select employeeId, department, attribute2, 2 rn from employees union all
select employeeId, department, attribute3, 3 rn from employees
)

SELECT * from a
SELECT * from e

Upvotes: 2

Related Questions