Sql pivot between 2 tables

Question

Table 1 (Resv)

ResvID  |  ResvDateTime           | BufferInd
-----------------------------------------------
   1    | 2012-06-11 08:30:00.000 |    1
   2    | 2012-06-11 08:30:00.000 |    2
   4    | 2013-07-20 12:00:00.000 |    1
   5    | 2013-07-20 12:00:00.000 |    2

note: ResvID(int identity), ResvDateTime(datetime)

Table 2 (Buffer)

BufferInd  |  BufferPeriod (minutes)
---------------------------------
   1       |     60
   2       |     120

note: BufferInd(int), BufferPeriod(int)

I would like to combine these 2 table using pivot with the both result below in SQL view

 ResvID |     ResvDateTime        |  BufferInd1   |     BufferInd2          
---------------------------------------------------------------------
   1    | 2012-06-11 08:30:00.000 |       1       |         2
   2    | 2013-07-20 12:00:00.000 |       1       |         2 

.

 ResvID |     ResvDateTime        |  DateTimeAfterOneHour   |     DateTimeAfterTwoHour          
--------------------------------------------------------------------------------------
   1    | 2012-06-11 08:30:00.000 | 2012-06-11 09:30:00.000 | 2012-06-11 10:30:00.000 
   2    | 2013-07-20 12:00:00.000 | 2013-07-20 13:00:00.000 | 2013-07-20 14:00:00.000 

note:

DateTime1 = DateAdd(hour, BufferPeriod/60, ResvDateTime) WHERE BufferInd = 1

DateTime2 = DateAdd(hour, BufferPeriod/60, ResvDateTime) WHERE BufferInd = 2

And this is my try which declare all variables in a table, it failed.

Create Table Resv
  ([ResvID] int, [BufferTypeInd] int ,[ResvDT] datetime, [BufferPeriod] int)
;

Insert Into Resv
  ([ResvID], [BufferTypeInd], [ResvDT], [BufferPeriod])
Values
  (1, 1, '2012-06-11 08:30:00.000', 60),
  (2, 2, '2012-06-11 08:30:00.000', 180),
  (4, 1, '2013-07-20 12:00:00.000', 60),
  (5, 2, '2013-07-20 12:00:00.000', 180),
;

My pivot try:

SELECT *
FROM
(
  (SELECT 
      [BufferTypeInd], [ResvDT], [BufferPeriod]
    FROM Resv)
  
) AS source

PIVOT
(
    MAX([ResvDT])
    FOR [BufferTypeInd] IN ([1],[2],[3])
) as pvt;


SELECT ResvID,
     MAX(
    CASE WHEN 
      BufferTypeInd = '1' 
    THEN 
      DateAdd(hour, BufferPeriod, ResvDT) 
    ELSE 
      NULL 
    END) [1],

    MAX(
    CASE WHEN 
      BufferTypeInd = '2' 
    THEN 
      DateAdd(hour, BufferPeriod, ResvDT) 
    ELSE 
      NULL 
    END) [2]
FROM Resv
GROUP BY ResvID

My SQL Fiddle Try (link)

Please help point out my problems and show me how the pivot and aggregation function should be done.Thanks.

Answer 1

Note:

Your schema doesn't make sense. If you are combining the Reservations purely by the ResvDT column, then it's too fickle. Two reservations at the same time will become indistinguishable for the purposes of pivoting. I have assumed your ResvID columns are the same for the same reservation. Otherwise, if the ResvDT is unique on its own, you can remove it from the PIVOT source in the query below.

Data

Create Table Resv
  ([ResvID] int, [ResvDT] datetime, [BufferTypeInd] int)
;

Insert Into Resv
  ([ResvID], [ResvDT], [BufferTypeInd])
Values
  (1, '2012-06-11 08:30:00.000', 1),
  (1, '2012-06-11 08:30:00.000', 3),
  (1, '2012-06-11 08:30:00.000', 4),
  (2, '2013-07-20 12:00:00.000', 1),
  (2, '2013-07-20 12:00:00.000', 3),
  (2, '2013-07-20 12:00:00.000', 4)
;

Create Table Buffer
 (BufferTypeInd int, BufferPeriod int)
;

Insert into Buffer values
 (1, 60),
 (3, 180),
 (4, 240);

Query

 declare @cols nvarchar(max), @names nvarchar(max);
  select @cols = isnull(@cols + ',', '')
               + QuoteName(RTrim(BufferPeriod)),
         @names = isnull(@names + ',', '')
                + QuoteName(RTrim(BufferPeriod))
                + ' as DateTimeAfter' + RTrim(BufferPeriod) + 'Minutes'
    from Buffer
order by BufferPeriod;

 declare @nsql nvarchar(max);
  select @nsql = N'
SELECT ResvID, ResvDT, ' + @names + '
FROM
(
  (SELECT R.ResvID, R.[ResvDT], B.[BufferPeriod],
          DateAdd(minute,B.BufferPeriod,R.ResvDT) TimeAfter
     FROM Resv R
     JOIN Buffer B on B.BufferTypeInd = R.BufferTypeInd)

) AS source
PIVOT
(
    MAX(TimeAfter)
    FOR [BufferPeriod] IN (' + @cols + ')
) as pvt';

    exec (@nsql);

Results

| RESVID |                      RESVDT |      DATETIMEAFTER60MINUTES |     DATETIMEAFTER180MINUTES |     DATETIMEAFTER240MINUTES |
----------------------------------------------------------------------------------------------------------------------------------
|      1 | June, 11 2012 08:30:00+0000 | June, 11 2012 09:30:00+0000 | June, 11 2012 11:30:00+0000 | June, 11 2012 12:30:00+0000 |
|      2 | July, 20 2013 12:00:00+0000 | July, 20 2013 13:00:00+0000 | July, 20 2013 15:00:00+0000 | July, 20 2013 16:00:00+0000 |

SQL Fiddle