Have a look at below example:
DECLARE @tmp TABLE (EmployeeName VARCHAR(30), EmployeeNo VARCHAR(30), [Type] VARCHAR(30), StartDate DATETIME, EndDate DATETIME)
INSERT INTO @tmp (EmployeeName, EmployeeNo, [Type] , StartDate, EndDate)
SELECT 'Dumitru', 'M1975012200006', 'TRAVEL', '06/03/2013', '06/03/2013'
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'TRAVEL', '06/17/2013', '06/17/2013'
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'TRAVEL', '06/03/2013', '06/03/2013'
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'TRAVEL', '06/15/2013', '06/15/2013'
UNION ALL SELECT 'Dumitru', 'M1975012200006', 'WORK', '06/04/2013', '07/15/2013'
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'WORK', '06/18/2013', '09/01/2013'
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'WORK', '06/04/2013', '07/15/2013'
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'WORK', '06/16/2013', '07/25/2013'
UNION ALL SELECT 'Dumitru', 'M1975012200006', 'LEAVE', '07/17/2013', '09/01/2013'
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'LEAVE', '09/03/2013', '10/15/2013'
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'LEAVE', '07/17/2013', '09/01/2013'
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'LEAVE', '07/27/2013', '09/02/2013'
IF OBJECT_ID(N'#pvtsrc',N'U') IS NOT NULL
DROP TABLE #pvtsrc
CREATE TABLE #pvtsrc (EmployeeName VARCHAR(30), EmployeeNo VARCHAR(30), TypeID VARCHAR(1), CurrDate DATETIME)
;WITH Days AS
(
SELECT EmployeeName, EmployeeNo, LEFT([Type],1) AS TypeID, StartDate, EndDate, StartDate AS CurrDate
FROM @tmp
UNION ALL
SELECT EmployeeName, EmployeeNo, TypeID, StartDate, EndDate, DATEADD(dd,1,CurrDate) AS CurrDate
FROM Days
WHERE DATEADD(dd,1,CurrDate)<=EndDate
)
INSERT INTO #pvtsrc (EmployeeName, EmployeeNo, TypeID, CurrDate)
SELECT EmployeeName, EmployeeNo, TypeID, CurrDate
FROM Days
ORDER BY EmployeeName, CurrDate
DECLARE @cols VARCHAR(1000)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)
DECLARE @ft VARCHAR(MAX)
DECLARE @startDay DATETIME
DECLARE @endDay DATETIME
DECLARE @currDay DATETIME
SET @startDay = '2013-06-01'
SET @endDay = '2013-06-30'
SET @currDay = @startDay
SET @cols = ''
WHILE (@currDay <= @endDay)
BEGIN
SET @cols = @cols + '[' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],'
SET @currDay = DATEADD(dd,1,@currDay)
END
SET @cols = LEFT(@cols, LEN(@cols)-1)
SET @dt = N'SELECT EmployeeName, EmployeeNo, TypeID, DAY(CurrDate) AS xDay
FROM #pvtsrc
WHERE CurrDate BETWEEN ''' + CONVERT(VARCHAR(10),@startDay,121) + ''' AND ''' + CONVERT(VARCHAR(10),@endDay,121) + ''''
SET @pt = N'SELECT EmployeeName, EmployeeNo, ' + @cols + ' ' +
'FROM(' + @dt + ') AS DT ' +
'PIVOT(MAX(TypeID) FOR xDay IN(' + @cols + ')) AS PT'
SET @cols = ''
SET @currDay = @startDay
WHILE (@currDay <= @endDay)
BEGIN
SET @cols = @cols + 'ISNULL([' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],''L'') AS [' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],'
SET @currDay = DATEADD(dd,1,@currDay)
END
SET @cols = LEFT(@cols, LEN(@cols)-1)
SET @ft = N'SELECT EmployeeName, EmployeeNo, ' + @cols + ' ' +
'FROM (' + @pt + ') AS FT '
EXEC(@ft)
DROP TABLE #pvtsrc
Result:
EmpName EmpNo 1 2 3 4 ... 30
Dumitru M1975012200006 L L T W ... W
Mottaghi M1975040500009 L L L W ... W
Dehghan M1975111100029 L L L L ... W
Alvarado M1986092400012 L L T W ... W