T-SQL Query | [ The Fishbone Salary Puzzle ]  – In this puzzle we have to find which employee is getting salary hikes across different months in a fishbone structure. Please check out the sample input and expected output for details.

Sample Input

 emp_name sal_date sal_amt Dick 20-06-1996 500 Harry 20-07-1996 500 Harry 20-09-1996 700 Tom 20-06-1996 500 Tom 20-08-1996 700 Tom 20-10-1996 800 Tom 20-12-1996 900

Expected Output

 emp_name 20-06-1996 20-07-1996 20-08-1996 20-09-1996 20-10-1996 20-12-1996 Dick 500 NULL NULL NULL NULL NULL Harry NULL 500 NULL 700 NULL NULL Tom 500 NULL 700 NULL 800 900

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` --Create Table CREATE TABLE Salaries (emp_name CHAR(10) NOT NULL, sal_date DATE NOT NULL, sal_amt DECIMAL (8,2) NOT NULL, PRIMARY KEY (emp_name, sal_date)); --Insert Data INSERT INTO Salaries VALUES ('Tom', '1996-06-20', 500.00), ('Tom', '1996-08-20', 700.00), ('Tom', '1996-10-20', 800.00), ('Tom', '1996-12-20', 900.00), ('Dick', '1996-06-20', 500.00), ('Harry', '1996-07-20', 500.00), ('Harry', '1996-09-20', 700.00); --Verify Data SELECT * FROM Salaries ```

Update May 14 | Solution

 ``` -- /************ SOLUTION 1 | Pawan Kumar Khowal ****************/ SELECT Emp_Name,[1996-06-20],[1996-07-20],[1996-08-20],[1996-09-20],[1996-10-20],[1996-12-20] FROM Salaries PIVOT ( MAX(sal_amt) FOR sal_date IN ([1996-06-20],[1996-07-20],[1996-08-20],[1996-09-20],[1996-10-20],[1996-12-20]) )p -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com