Maximus Decimus Maximus Decimus - 7 months ago 8
SQL Question

How to merge multiple rows in one single rows

I have the following temp table that has been built to list current room status in a hotel

declare @CurrentDate datetime = '2016-04-21'
select Lodging, Room, FirstName, LastName, TempStatus, @CurrentDate as CurrentDate
from RoomAvailability


This is the following result:

Lodging Room FirstName LastName TempStatus CurrentDate
-------------------------------------------------------------------------------
marriok 119 Super Man Next guest 2016-03-24 00:00:00.000
marriok 101 Bat Man Next guest 2016-03-24 00:00:00.000
marriok 123 Aqua Man Leaving today 2016-03-24 00:00:00.000
marriok 103 Wonder Woman Leaving today 2016-03-24 00:00:00.000
marriok 101 Lex Luthor Leaving today 2016-03-24 00:00:00.000


This is my expected result:

Lodging Room CurrFirstName CurrLastName TempStatus CurrentDate NextFistName NextLastName TempStatus
----------------------------------------------------------------------------------------------------------------------------------------------
marriok 119 Super Man Next guest 2016-03-24 00:00:00.000 NULL NULL NULL
marriok 123 Aqua Man Leaving today 2016-03-24 00:00:00.000 NULL NULL NULL
marriok 103 Wonder Woman Leaving today 2016-03-24 00:00:00.000 NULL NULL NULL
marriok 101 Lex Luthor Leaving today 2016-03-24 00:00:00.000 Bat Man Next guest


I tried this:

declare @CurrentDate datetime = '2016-04-21'
select coalesce(a.Lodging, b.Lodging) as Lodging,
coalesce(a.Room, b.Room) as Room,
a.FirstName as CurrFirstName, a.LastName as CurrLastName, a.TempStatus, @CurrentDate,
b.NextFirstName, b.NextLastName, b.TempStatus
from RoomAvailability a
full join RoomAvailability b
on a.Lodging = b.Lodging
and a.Room = b.Room


And I got this:

Lodging Room CurrFirstName CurrLastName TempStatus CurrentDate NextFistName NextLastName TempStatus
----------------------------------------------------------------------------------------------------------------------------------------------
marriok 119 Super Man Next guest 2016-03-24 00:00:00.000 Super Man Next guest
marriok 101 Bat Man Next guest 2016-03-24 00:00:00.000 Bat Man Next guest
marriok 101 Bat Man Next guest 2016-03-24 00:00:00.000 Lex Luthor Leaving today
marriok 123 Aqua Man Leaving today 2016-03-24 00:00:00.000 Aqua Man Leaving today
marriok 103 Wonder Woman Leaving today 2016-03-24 00:00:00.000 Wonder Woman Leaving today
marriok 101 Lex Luthor Leaving today 2016-03-24 00:00:00.000 Bat Man Next guest
marriok 101 Lex Luthor Leaving today 2016-03-24 00:00:00.000 Lex Luthor Leaving today


So how can I get unique or merged rows and listing the people who is leaving and arriving at the same room on the same date? (Preferable without CTE)




Update

The temp table RoomAvailability was product of the 3 select statements with union all

The 3 select statements are the same, they just differ on the where clause. In this 3 select statements contain a check-in and check-out datetime columns.

My 1st select is filtered by the current date variable between the check-in and check-out date (non-inclusive). I added a column as status as 'In house'.

My 2nd select is filtered by current date variable equals to check-in and I added the status text 'Next guest'

The last select is filtered by current date variable equals to check-out and I added the status 'Leaving today'.




Update 2

This is closer that I could get without CTE

select coalesce(a.Lodging, b.Lodging),
coalesce(a.Room, b.Room),
a.FirstName, a.LastName, a.TempStatus,
b.FirstName, b.LastName, b.TempStatus,
@CurrentDate
from RoomAvailability a
left join RoomAvailability b
on a.Lodging = b.Lodging
and a.Room = b.Room
and a.TempStatus != b.TempStatus


But it's repeating the row for the same room at the end

marriok 119 Super Man Next guest NULL NULL NULL 2016-04-21
marriok 101 Bat Man Next guest Lex Luthor Leaving today 2016-04-21
marriok 123 Aqua Man Leaving today NULL NULL NULL 2016-04-21
marriok 103 Wonder Woman Leaving today NULL NULL NULL 2016-04-21
marriok 101 Lex Luthor Leaving today Bat Max Next guest 2016-04-21 --<-- this should not be displayed

Answer

If you were on SQL 2012 and later, the finaly query would be simpler.

Assuming your data is in the table named #t

Setup data

create table #t (
    Lodging varchar(10)
    , Room int
    , FirstName  varchar(10)
    , LastName  varchar(10)
    , TempStatus  varchar(20)
    , CurrentDate datetime
)

insert into #t values
('marriok'     ,119     ,'Super'       ,'Man'     ,'Next guest'      ,'2016-03-24'),
('marriok'     ,101     ,'Bat'         ,'Man'     ,'Next guest'      ,'2016-03-24'),
('marriok'     ,123     ,'Aqua'        ,'Man'     ,'Leaving today'   ,'2016-03-24'),
('marriok'     ,103     ,'Wonder'      ,'Woman'   ,'Leaving today'   ,'2016-03-24'),
('marriok'     ,101     ,'Lex'         ,'Luthor'  ,'Leaving today'   ,'2016-03-24')

Your query will be

with sortIt as (
    select *, case TempStatus 
                  when 'Next guest' then 2 
                  when 'Leaving today' then 1 
              end as Sort 
    from #t
)
, addFld as (
    select t1.*, t2.FirstName as NextFirstName, t2.LastName as NextLastName
        , t2.TempStatus as NextTempStatus 
    from sortIt t1
        left join sortIt t2 on t2.Room = t1.Room and t2.Sort = t1.Sort + 1
)
, removDup as ( 
    select *, ROW_NUMBER() over (partition by Room order by NextFirstName desc) rn
    from addFld
)
select * from removDup
where rn = 1

Result

+---------+------+-----------+----------+---------------+-------------------------+------+---------------+--------------+----------------+----+
| Lodging | Room | FirstName | LastName |  TempStatus   |       CurrentDate       | Sort | NextFirstName | NextLastName | NextTempStatus | rn |
+---------+------+-----------+----------+---------------+-------------------------+------+---------------+--------------+----------------+----+
| marriok |  101 | Lex       | Luthor   | Leaving today | 2016-03-24 00:00:00.000 |    1 | Bat           | Man          | Next guest     |  1 |
| marriok |  103 | Wonder    | Woman    | Leaving today | 2016-03-24 00:00:00.000 |    1 | NULL          | NULL         | NULL           |  1 |
| marriok |  119 | Super     | Man      | Next guest    | 2016-03-24 00:00:00.000 |    2 | NULL          | NULL         | NULL           |  1 |
| marriok |  123 | Aqua      | Man      | Leaving today | 2016-03-24 00:00:00.000 |    1 | NULL          | NULL         | NULL           |  1 |
+---------+------+-----------+----------+---------------+-------------------------+------+---------------+--------------+----------------+----+
Comments