or
sql-server sql-server-2008
Federico Giust imported from SE
Let's say I have Table A: *BookingsPerPerson*

    Person_Id    ArrivalDate    DepartureDate
    123456       2012-01-01     2012-01-04
    213415       2012-01-02     2012-01-07

What I need to achieve with a view is the following:

    Person_Id    ArrivalDate    DepartureDate    Jan-01    Jan-02    Jan-03    Jan-04    Jan-05    Jan-06    Jan-07
    123456       2012-01-01     2012-01-04       1         1         1         1
    213415       2012-01-02     2012-01-07                 1         1         1         1         1         1

The system is for events, so each hotel booking could take anything between 1 to 15 days but no more than that. Any ideas would be very much appreciated. 
Top Answer
taryn imported from SE
You can use the `PIVOT` function to perform this query.  My answer will include both a *static* and a *dynamic* version because sometimes it is easier to understand it using a static version.

A *static pivot* is when you hard-code all of the values that you want to transform into columns.

    -- first into into a #temp table the list of dates that you want to turn to columns
    ;with cte (datelist, maxdate) as
    (
    	select min(arrivaldate) datelist, max(departuredate) maxdate
    	from BookingsPerPerson
    	union all
    	select dateadd(dd, 1, datelist), maxdate
    	from cte
    	where datelist < maxdate
    ) 
    select c.datelist
    into #tempDates
    from cte c
    
    select *
    from
    (
    	select b.person_id, b.arrivaldate, b.departuredate,
    		d.datelist,
    		convert(CHAR(10), datelist, 120) PivotDate
    	from #tempDates d
    	left join BookingsPerPerson b
    		on d.datelist between b.arrivaldate and b.departuredate
    ) x
    pivot
    (
    	count(datelist)
    	for PivotDate in ([2012-01-01], [2012-01-02], [2012-01-03],
    			  [2012-01-04], [2012-01-05], [2012-01-06] , [2012-01-07])
    ) p;

Results (See [SQL Fiddle With Demo][1]):

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
--|--|--|--|--|--|--|--|--|--
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1

The dynamic version will generate the list of values to transform to columns:

    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    
    ;with cte (datelist, maxdate) as
    (
    	select min(arrivaldate) datelist, max(departuredate) maxdate
    	from BookingsPerPerson
    	union all
    	select dateadd(dd, 1, datelist), maxdate
    	from cte
    	where datelist < maxdate
    ) 
    select c.datelist
    into #tempDates
    from cte c
    
    
    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), datelist, 120)) 
                        from #tempDates
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT person_id, arrivaldate, departuredate, ' + @cols + ' from 
                 (
                   	select b.person_id, b.arrivaldate, b.departuredate,
    					d.datelist,
    					convert(CHAR(10), datelist, 120) PivotDate
    				from #tempDates d
    				left join BookingsPerPerson b
    					on d.datelist between b.arrivaldate and b.departuredate
                ) x
                pivot 
                (
    				count(datelist)
                    for PivotDate in (' + @cols + ')
                ) p '
    
    execute(@query)

The results are the same (see [SQL Fiddle With Demo][2]):

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
--|--|--|--|--|--|--|--|--|--
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1


  [1]: http://sqlfiddle.com/#!3/8857c/9
  [2]: http://sqlfiddle.com/#!3/8857c/14
Answer #2
aaron bertrand imported from SE
I'm old school, and find `CASE` easier to work out in my head than `PIVOT`. I'm sure bluefeet will show up shortly and put me to shame, but in the meantime you can play with this dynamic SQL query. Assuming your table stores `DATE` and not `DATETIME` (or even worse, `VARCHAR`):

    USE tempdb;
    GO

    CREATE TABLE dbo.a
    (
       Person_Id INT, 
       ArrivalDate DATE, 
       DepartureDate DATE
    );
    
    INSERT dbo.a SELECT 123456, '2012-01-01', '2012-01-04'
    UNION ALL    SELECT 213415, '2012-01-02', '2012-01-07';
    
    DECLARE @sql NVARCHAR(MAX) = N'SELECT Person_Id';
    
    ;WITH dr AS
    (
      SELECT MinDate = MIN(ArrivalDate),
             MaxDate = MAX(DepartureDate)
      FROM dbo.a
    ),
    n AS
    (
      SELECT TOP (DATEDIFF(DAY, (SELECT MinDate FROM dr), (SELECT MaxDate FROM dr)) + 1)
       d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, 
         (SELECT MinDate FROM dr))
     FROM sys.all_objects
    )
    SELECT @sql += ',
      ' + QUOTENAME(d) + ' = CASE WHEN ''' + CONVERT(CHAR(10), d, 120) 
      + ''' BETWEEN ArrivalDate AND DepartureDate THEN ''1'' ELSE '''' END' FROM n;
    
    SELECT @sql += ' FROM dbo.a;'
    
    EXEC sp_executesql @sql;
    GO

    DROP TABLE dbo.a;

One of the very few cases, BTW, where I could justify using `BETWEEN` for date range queries.
Return a column per date in a range

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.