I was asked to pivot this data using basic SQL and wasn't sure how to answer it. I googled some answers and realized you can use MAX or SUM with CASE expressions, but at the end of the interview I asked how you would solve the question and the interviewer said by using joins. Can anyone show me how it's done using joins?

BEGINNING TABLE

Code to create the beginning table

CREATE TABLE Employees(emp_id INT, col_id INT, col_desc NVARCHAR(MAX), attvalue INT, month DATE);

INSERT INTO Employees
VALUES
(1,1,'salary',2000,'2010-05-09'),
(1,2,'bonus',0,'2010-05-09'),
(1,3,'compensation',2000,'2010-05-09'),
(1,1,'salary',2000,'2010-05-10'),
(1,2,'bonus',500,'2010-05-10'),
(1,3,'compensation',2500,'2010-05-10'),
(2,1,'salary',1000,'2010-05-09'),
(2,2,'bonus',500,'2010-05-09'),
(2,3,'compensation',1500,'2010-05-09');

RESULTING TABLE

CodePudding user response：

Below are the self join, case expression and pivot way

-- Self Join way
select s.emp_id, s.month, 
       s.attvalue as salary,
       b.attvalue as bonus,
       c.attvalue as compensation
from   Employees s
       inner join Employees b on  s.emp_id   = b.emp_id
                             and  s.month    = b.month
       inner join Employees c on  s.emp_id   = c.emp_id
                             and  s.month    = c.month
where  s.col_desc = 'salary'
and    b.col_desc = 'bonus'
and    c.col_desc = 'compensation'
order by s.emp_id, s.month

-- case expression way
select emp_id, month,
       max(case when col_desc = 'salary' then attvalue else 0 end) as salary,
       max(case when col_desc = 'bonus'  then attvalue else 0 end) as bonus,
       max(case when col_desc = 'compensation' then attvalue else 0 end) as compensation
from   Employees
group by emp_id, month
order by emp_id, month

-- Pivot way
select *
from   (
           select emp_id, month, col_desc, attvalue
           from Employees
       ) d
       pivot
       (
           max(attvalue)
           for col_desc in ([salary], [bonus], [compensation])
       ) p
order by emp_id, month