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.
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
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.