Something like this...

DECLARE@ShiftScheduler TABLE

(

[Date]DATE,

EmpidINT,

[Shift]TINYINT,

[ShiftType]TINYINT

)

INSERT@ShiftScheduler

SELECT'20140101', 1424, 2, 4 UNION ALL

SELECT'20140102', 1424, 2, 4 UNION ALL

SELECT'20140103', 1424, 2, 4 UNION ALL

SELECT'20140104', 1424, 1, 2 UNION ALL

SELECT'20140101', 1425, 1, 2

SELECTEmpid,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 1 THEN [Shift] ELSE NULL END ) AS D1Shift,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 1 THEN [ShiftType] ELSE NULL END ) AS D1ShiftType,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 2 THEN [Shift] ELSE NULL END ) AS D2Shift,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 2 THEN [ShiftType] ELSE NULL END ) AS D2ShiftType,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 3 THEN [Shift] ELSE NULL END ) AS D3Shift,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 3 THEN [ShiftType] ELSE NULL END ) AS D3ShiftType,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 4 THEN [Shift] ELSE NULL END ) AS D4Shift,

MAX( CASE WHEN DATEPART( DAY, [Date] ) = 4 THEN [ShiftType] ELSE NULL END ) AS D4ShiftType

FROM@ShiftScheduler

WHERE[Date] BETWEEN '20140101' AND '20140104'

GROUP BY Empid

The link given by Steve explains the method used to solve this common problem

I have given the same below for your reference

http://www.sqlservercentral.com/articles/T-SQL/63681/

You would have received an answer much earlier had you followed the steps mentioned in the link in my signature.