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]

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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