title: How to merge 2 SQL CASE statements for output in a single column
body: <p>I'm using SQL Server 2014 and I have a query where I need to merge 2 CASE statements for output in a single column called, say, 'Market Final'.</p>
  
  <p>Case statement 1 is as follows:</p>
  
  <pre><code>(CASE e.TravelAgencyTypeCode WHEN 'DMC' THEN g2.CountryGroup ELSE g.CountryGroup END) AS 'Market Final'
  </code></pre>
  
  <p>When I run my query with the CASE statement above, it runs fine and I get exactly what I was looking for. It gets a bit more complicated for me when I need to add the second CASE Statement which also affects the "Market Final" column.</p>
  
  <p>CASE Statement 2 is as follows:</p>
  
  <pre><code>(CASE c.TAProfileID WHEN c.TAProfileID = '316' and c.CurrencyCode = 'MUR' THEN g.CountryGroup = 'DB Local' ELSE g.CountryGroup = 'DB International' END)
  </code></pre>
  
  <p>I've read that this can be done with the UNION ALL statement but I just can't figure out how to deal with it. I also have a feeling that my CASE Statement 2 is wrongly written.</p>
  
  <p>Thanks for your help!</p>
  
  <p>EDIT: Here is my full SQL Query so that you get the whole picture:</p>
  
  <pre><code>USE  MyDatabase
  
  SELECT 
  
    b.PropertyCode,
    c.PMSConfirmationNumber,   
    a.FirstName + ' ' + a.LastName AS 'Name',
    b.ReservationStatus AS 'Status',
    d.rsl_nationality AS 'Nationality',
    d.rsl_rateplan AS 'Rate Plan Code',
    d.rsl_roomtype AS 'Room Type',
    i.RoomType2 AS 'Room Type 2', 
    b.GuestCount AS 'Total Guest', 
    c.CurrencyCode,
    e.TravelAgencyTypeCode AS 'Source of Business',
    c.TAProfileID,
    e.Name AS 'Tour Operator', 
    g.CountryGroup AS 'Market', 
    c.TAProfileID2, 
    e2.Name AS 'Booking Origin (1)', 
    g2.CountryGroup AS 'Booking Origin (2)',
  
    (CASE e.TravelAgencyTypeCode WHEN 'DMC' THEN g2.CountryGroup ELSE  g.CountryGroup END) AS 'Market Final'
  
   FROM GuestNameInfo a
   JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
   LEFT JOIN ReservationStay c ON c.ReservationStayID = b.ReservationStayID
   LEFT JOIN P5RESERVATIONLIST d ON d.rsl_code = b.ReservationStayID 
   LEFT JOIN TravelAgency e ON e.TravelAgencyID = c.TAProfileID 
   LEFT JOIN Market g ON e.CountryCode = g.CountryCode 
   LEFT JOIN TravelAgency e2 ON e2.TravelAgencyID = c.TAProfileID2
   LEFT JOIN Market g2 ON e2.CountryCode = g2.CountryCode 
   LEFT JOIN Exrate h ON h.Ccode = c.CurrencyCode
   LEFT JOIN RoomCat i ON b.PropertyCode = i.Property AND d.rsl_roomtype = i.RoomType 
  </code></pre>
  
  <p>So now I need to add the CASE 2 Statement 2 to this query so that the changes are reflected in the 'Market Final" column.</p>
  
  <p>And here is the output I'm after:</p>
  
  <pre><code>PropertyCode....CurrencyCode SourceofBusiness TAProfileID.... Market .... Booking Origin (2)  MarketFinal
      A            MUR          DMC                 50           Other Mkt      Scandinavia       Scandinavia
      A            USD          TO                  75             UK             UK                UK
      B            USD          DIR                 316             DB             USA               DB International
      B            MUR          DIR                 316            DB             MTS               DB Local
  </code></pre>