T-SQL Query | [ Pivoting Data Puzzle ]

Puzzle Description

There was a question on facebook about pivoting data. Its very easy you just have to pivot data based on the empid and punchadate cities. Let’s go through the sample input and expected output below-

Pictorial presentation of the puzzle.(Pic taken from some Facebook group)

Pawan Kumar Khowal – Facebook Puzzle [Pivoting Data]

Sample Input

 empid punchdate punchtime 1 01-Aug 10:00:00 1 01-Aug 12:00:00 1 01-Aug 15:00:00 1 01-Aug 18:00:00 1 01-Aug 22:00:00 2 01-Aug 10:00:00 2 01-Aug 12:00:00 2 01-Aug 15:00:00 1 02-Aug 09:00:00 1 02-Aug 11:00:00 1 02-Aug 15:00:00 2 02-Aug 07:00:00 2 02-Aug 08:00:00 2 02-Aug 09:00:00 2 02-Aug 12:00:00 2 02-Aug 15:00:00

Expected Output

 empid punchdate 1 2 3 4 5 1 01-Aug 10:00:00 12:00:00 15:00:00 18:00:00 22:00:00 2 01-Aug 10:00:00 12:00:00 15:00:00 NULL NULL 1 02-Aug 09:00:00 11:00:00 15:00:00 NULL NULL 2 02-Aug 07:00:00 08:00:00 09:00:00 12:00:00 15:00:00

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 Punch ( empid INT ,punchdate VARCHAR(3) ,punchtime TIME(5) ) GO INSERT INTO Punch(empid,punchdate,punchtime) VALUES (1, '1/8','10:00'), (1, '1/8','12:00'), (1, '1/8','15:00'), (1, '1/8','18:00'), (1, '1/8','22:00'), (2, '1/8','10:00'), (2, '1/8','12:00'), (2, '1/8','15:00'), (1, '2/8','09:00'), (1, '2/8','11:00'), (1, '2/8','15:00'), (2, '2/8','07:00'), (2, '2/8','08:00'), (2, '2/8','09:00'), (2, '2/8','12:00'), (2, '2/8','15:00') GO ```

Update Sep 01 | Solution# 1 – Pawan Kumar Khowal

 ``` WITH CTE AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY empid,Punchdate ORDER BY (SELECT NULL)) rnk FROM Punch ) SELECT empid,punchdate,[1],[2],[3],[4],[5] FROM CTE PIVOT (MAX(punchtime) FOR rnk IN ([1],[2],[3],[4],[5])) p ```

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

Http://MSBISkills.com

Pawan Kumar Khowal