Displaying same column name in the same column

Hi, I'm trying to accomplish something with this code:

SELECT ProductionOrder.PO, ProductionOrder.Part, Single.Length,

Single.Date, Pairs.Length, Pairs.Date FROM [ProductionOrder]

LEFT OUTER JOIN Pairs ON Pairs.PO = [ProductionOrder].PO AND

Pairs.Part = [ProductionOrder].Part

LEFT OUTER JOIN Single ON Single.PO = [ProductionOrder].PO AND

Single.Part = [ProductionOrder].Part

WHERE (Single.Broke='True' AND (Single.[BrokeFixed] = 'False' OR Single.[BrokeFixed] IS NULL))

OR (Pairs.Broke='True' AND (Pairs.[BrokeFixed] = 'False' OR Pairs.[BrokeFixed] IS NULL))

With this I get the following result:

PO | Part | Length | Date | Length | Date

-

602520 | 3 | 24000 | 2007-08-24 15:33:33.727 | NULL | NULL
602521 | 3 | NULL | NULL | 14550 | 2007-08-29 17:41:01.930

But what I want is:

PO | Part | Length | Date

602520 | 3 | 24000 | 2007-08-24 15:33:33.727
602521 | 3 | 14550 | 2007-08-29 17:41:01.930

How can I accomplish this? Please, any help would be very much appreciated.

Thanks a lot in advance

You can use ISNULL or COALESCE.

eg SELECT ISNULL(Single.Date, Pairs.Date) AS Date, ISNULL(Single.Length, Pairs.Length) AS length

Remember, this method will always use Single.Date unless its NULL so if both are populated, then Single.Date will be displayed. This also won't check a dependancy on the Date and Length ie its possible that you could have a Single.Date with a Pairs.Length unless you are 100% sure this situation could never occur. If it might, you may want to consider using a CASE statement to choose which values to display.

Check CASE/ISNULL/COALESCE out in more detail in Books Online.

HTH!

|||

Thanks a lot! That's excatly what I needed. I'm not an expert T-SQL programmer so I was getting a hard time trying

to get this result. And no, they will never be both populated as there will never be the same PO number on both

Single and Pairs table. But both PO numbers will be on the ProductionOrder Table as all POs (ProductionOrders)

will be on the ProductionOrder table, but each PO has a corresponding table, defining its type with its details (Single, Pairs, Groups, and FinalProducts).

Thanks again. Both of you who tried to help me out.

Regards

Fábio