Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let me explain what I've got and what I'm trying to achieve.

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

Any ideas would be very much appreciated.

Thanks in advance

share|improve this question
1  
What have you so far? –  dezso Oct 11 '12 at 13:27
3  
So, if someone departed a year after arrival, you want a 365+ column result? –  ypercube Oct 11 '12 at 13:33
    
Only thing I can think of is to use dynamic SQL to populate a PIVOT query. Unfortunately, I don't have time today to flesh out a proper answer. –  Matt M Oct 11 '12 at 13:56
    
That will never be an option because the system is for events, so each hotel booking could take anything between 1 to 15 days but no more than that. –  Federico Giust Oct 11 '12 at 13:57
    
I've been looking at the pivot queries but I can't get my head around it to make it work. –  Federico Giust Oct 11 '12 at 13:57

2 Answers 2

up vote 14 down vote accepted

You can use the PIVOT function to perform this query. My answer will include both a Static and 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):

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):

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
share|improve this answer
1  
+1 I knew you'd fill the gap. :-) –  Aaron Bertrand Oct 11 '12 at 16:44
    
@AaronBertrand I cannot help it. –  bluefeet Oct 11 '12 at 16:47
1  
+1 But I would make the recommendation to parametrize the execution of your dynamic query, using EXECUTE sp_executesql. msdn.microsoft.com/en-us/library/ms188001%28v=sql.110%29.aspx –  Matt M Oct 11 '12 at 20:47
    
@MattM noted, thanks for the tip –  bluefeet Oct 11 '12 at 20:51
    
@MattM I certainly agree on the preference of sp_executesql over EXEC, but just to clarify, the solution can't parameterize anything - @cols must be built into the string, SQL Server isn't going to know what to do with that if you pass it in as a parameter. –  Aaron Bertrand Oct 11 '12 at 22:48

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.

share|improve this answer
    
Thanks but the other solution work better for us. –  Federico Giust Oct 15 '12 at 15:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.