SQLTeam.com | Weblogs | Forums

Combine data from 2 columns into 1...?


#1

Evening all,

I'm getting rather confused, I have a query that produces 6 columns, but I only want 5 columns. 2 of the columns contain data that I want to merge but I'm getting really confused about how to achieve it...

Probably the best way to describe is give you code & example so here goes.....

Declare @Test_ID Int = 2970,
        @StartDate Date = '2016-03-31',
        @EndDate Date = '2016-03-31';

SELECT 
T_TID, 
T_FName, 
T_LName, 
A.*, 
B.*, 
TR_Rating
 
FROM Empolyees 
FULL OUTER JOIN Ratings 
ON TR_TID = T_TID 
AND TR_SID = @Test_ID
 
OUTER APPLY 
    (SELECT TOP 1 A_Colour 
    FROM Availability
    WHERE A_TID = T_TID 
        AND A_AvailDate >= @StartDate 
        AND A_AvailDate <= @EndDate 
    GROUP BY A_Colour 
    ORDER BY COUNT(*) Desc)A
    
OUTER APPLY 
    (SELECT TOP 1
        CASE 
        WHEN B_BID > 0 THEN 'Yellow'
        END AS BookingColour
    FROM Bookings 
    WHERE B_TID = T_TID 
        AND B_StartDate >= @StartDate 
        AND B_EndDate <= @EndDate 
    GROUP BY B_BID 
    ORDER BY COUNT(*) Desc)B
    
WHERE T_Status = 'Live'

The current output is something like this:

I would like to keep 'A_Colour' and do away with 'BookingColour'. The only rule is that 'Booking Colour' over rides 'A_Colour' so in the example line 6 would be 'Yellow' not 'Red' and line 9 'Yellow' not NULL....

Originally I was trying to achieve it with the REPLACE command but couldn't get it to work properly... Any ideas how I might achieve this.....?

Thanks

Dave


#2
select ...
      ,isnull(b.bookingcolour,a.a_colour) as a_colour
      ,...
  from ...
  .
  .
  .

#3

Such a simple answer, I love it....!!

However one slight glitch, having 2 columns called 'a_colour' made it a little buggy, didn't always get the expected results, change it to 'a_colour2' and it works perfectly....

isnull(b.b_colour,a.a_colour) as a_colour2

thank you bitsmed...!


#4

Hi, Please try this one sample query it may be helpful to you:

SELECT C.Document_Name as Document_Name

FROM tblClientDocument_Base C

UNION -- UNION ALL if you know there can not be dups:

SELECT J.Document_Name

FROM tblJobDocument_Base J

ORDER BY Document_Name