Merge data from columns into one column

Question

I have three different tables and i need same column from the three tables and i need to put all of the values into one output column without missing any record. below is the existing query suggest me some modifications

    SELECT 
    dbo.dealer_program_details.o_comments,
    dbo.Self_Am.o_comments,
    dbo.Coop_Payment_Detail.o_comments
    FROM
    dbo.dealer_program_details
    INNER JOIN
    dbo.Self_Am
    ON
    (
        dbo.dealer_program_details.DEALER_CODE = dbo.Self_Am.Dealer_Code)
    INNER JOIN
    dbo.Coop_Payment_Detail
   ON
    (
        dbo.dealer_program_details.DEALER_CODE = dbo.Coop_Payment_Detail.Dealer_Code)

now i want all these three columns into one single column

Answer 1

If you want them in one column, then concatenate them together:

SELECT (dbo.dealer_program_details.o_comments + dbo.Self_Am.o_comments +
        dbo.Coop_Payment_Detail.o_comments
       ) as OneColumn
FROM dbo.dealer_program_details INNER JOIN
     dbo.Self_Am
     ON dbo.dealer_program_details.DEALER_CODE = dbo.Self_Am.Dealer_Code INNER JOIN
     dbo.Coop_Payment_Detail
     ONdbo.dealer_program_details.DEALER_CODE = dbo.Coop_Payment_Detail.Dealer_Code;

In Sybase, you don't have to worry about NULL values, because these are treated as empty strings. In SQL Server, the result will be NULL if any column values are NULL.

EDIT:

You can choose the first non-NULL row, by using coalesce():

SELECT coalesce(dbo.dealer_program_details.o_comments, dbo.Self_Am.o_comments,
        dbo.Coop_Payment_Detail.o_comments
       ) as OneColumn

But if two (or more) columns have comments, then you will only keep the first non-NULL one.