How to merge multiple rows in one single rows

Question

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

put your query in a subquery, add row_number, partition by room and order by tempstatus.. then in the outer query select where row_number = 1.. here's one example http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — JamieD77, Apr 21 '16 at 20:29
@FLICKER, well just the id of the lodging and the room. This table has been built from a query with joins in order to the get the summary of the day, but we want no duplicate. — Maximus Decimus, Apr 21 '16 at 20:48
What is the order of the records? i see all records are in the same date so for Room 101, I don't know which row is older and which row is newer. if you tell me what is the order of the rows, I'll be able to make your query. — FLICKER, Apr 21 '16 at 20:53
You are right, there is no specific order. It's a hotel room reservation. So for the same date you have 2 choices for a one room; someone is arriving and someone is leaving. If not, imagine that the date is yesterday, I will have those who are in "Leaving today" as in "In house" and those who are today as "Next guest" won't exist. I updated what the means of the alias of my table. — Maximus Decimus, Apr 21 '16 at 21:01

FLICKER · Accepted Answer · 2016-04-21T21:50:59.023

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

I don't know what you mean with Sql2012. This also works with 2008, it's the use of CTE. I thought that it could be easier with full join, but I give up. Because I don't understand CTEs so much, but thanks your answer saved the day. Very appreciated!! — Maximus Decimus, Apr 22 '16 at 04:14
In sql 2012 you can use LAG and LEAD functions that make the final query much shorter. Anyway, I'm glad it helped — FLICKER, Apr 22 '16 at 04:20

How to merge multiple rows in one single rows

1 Answers1