sql-server sql-server-2008 add tag
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.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.