Columns to Rows in Sql Server with multiple data

Question

please help.. please see example data below and the desired output..

Data:

EmployeeID    Date        In          Out
00001         01/01/2018 | 07:00 AM | 05:00 PM
00001         01/02/2018 | 07:00 AM | 05:00 PM
00001         01/03/2018 | 07:00 AM | 05:00 PM
00002         01/01/2018 | 07:00 AM | 05:00 PM
00002         01/02/2018 | 07:00 AM | 05:00 PM
00002         01/03/2018 | 07:00 AM | 05:00 PM

Desired Output

EmployeeID    01/01/2018         |  01/02/2018         |  01/03/2018
00001         07:00 AM - 05:00PM |  07:00 AM - 05:00PM |  07:00 AM - 05:00PM
00002         07:00 AM - 05:00PM |  07:00 AM - 05:00PM |  07:00 AM - 05:00PM

you can find many solution in SO. Keyword is pivot. If you know already about it. then what you tried yet..? — Pugal
– Pugal, Commented Sep 11, 2018 at 9:58

Paweł Dyl · Accepted Answer · 2018-09-11 10:02:24Z

2

Use PIVOT:

WITH Src AS
(
  SELECT * FROM (VALUES 
  ('00001' ,'01/01/2018', '07:00 AM', '05:00 PM'),
  ('00001' ,'01/02/2018', '07:00 AM', '05:00 PM'),
  ('00001' ,'01/03/2018', '07:00 AM', '05:00 PM'),
  ('00002' ,'01/01/2018', '07:00 AM', '05:00 PM'),
  ('00002' ,'01/02/2018', '07:00 AM', '05:00 PM'),
  ('00002' ,'01/03/2018', '07:00 AM', '05:00 PM')) T(EmployeeID,[Date],[In],[Out])
)
SELECT * FROM
(SELECT EmployeeID, [Date], [In]+' - '+[Out] [In] FROM Src) T
PIVOT (MAX([In]) FOR [Date] IN ([01/01/2018],[01/02/2018],[01/03/2018])) AS P

answered Sep 11, 2018 at 10:02

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Genebert Over a year ago

how can i dynamically call something like this sir.. declare @xxx nvarchar(200) = '01/01/2018' select @xxx from pivot

Collectives™ on Stack Overflow

Columns to Rows in Sql Server with multiple data

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related