USE tempdb
-- Test data
CREATE TABLE dbo.Reservations
(
    PersonID    integer NOT NULL,
    CheckIn     date NOT NULL,
    CheckOut    date NOT NULL,
    
    CHECK (CheckOut >= CheckIn)
)
GO
INSERT dbo.Reservations
    (PersonID, CheckIn, CheckOut)
VALUES
    (1, {D '2012-01-02'}, {D '2012-01-05'}),
    (2, {D '2012-01-01'}, {D '2012-01-04'})

CREATE TABLE #Unpivoted
(
    PersonID    integer NOT NULL,
    TheDate     date NOT NULL,
    
    PRIMARY KEY (TheDate, PersonID),
    UNIQUE (PersonID, TheDate)
);

-- Standard in-line numbers table
WITH
    N1 AS (SELECT N1.n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 (n)),
    N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
    N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R),
    Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) AS n FROM N4)
-- Unpivot so there is one row per reservation date per person
INSERT #Unpivoted
    (PersonID, TheDate)
SELECT
    R.PersonID,
    Occupied.TheDate
FROM dbo.Reservations AS R
CROSS APPLY 
(
    SELECT TOP(DATEDIFF(DAY, R.CheckIn, R.CheckOut)) 
        DATEADD(DAY,Numbers.n - 1, R.CheckIn)
    FROM Numbers 
    ORDER BY 
        Numbers.n
) AS Occupied (TheDate)

-- Dynamic SQL for the cross-tab
DECLARE
    @SQL nvarchar(max) = 
        N'SELECT U.PersonID ' +
        (
            -- Construct the dynamic pivot
            SELECT 
                N',SUM(CASE WHEN U.TheDate = ''' + 
                CA.date_string + 
                N''' THEN 1 ELSE 0 END) AS ' + 
                QUOTENAME(CA.date_string)
            FROM 
            (
                -- Unique dates in the unpivoted set
                SELECT 
                    U.TheDate, 
                    CONVERT(char(8), U.TheDate, 112) AS date_string 
                FROM #Unpivoted AS U 
                GROUP BY U.TheDate
            ) AS CA
            ORDER BY
                CA.TheDate
            FOR XML
                PATH (''), TYPE
        ).value('./text()[1]', 'NVARCHAR(MAX)') + 
        N' FROM #Unpivoted AS U GROUP BY U.PersonID'

-- For debugging
SELECT @SQL

-- Return results
EXECUTE (@SQL)

-- Tidy up
DROP TABLE #Unpivoted
DROP TABLE dbo.Reservations