SQL make 2 columns into 1 from 1 table keep all rows in SELECT query-CodePudding

I am dealing with a DB whose transaction table could have been split up into 2, but dev team decided it was not worth it.

Now I want to retrieve the transactions and their dates. Problem is : table includes 2 different types of transactions, meaning: I have 2 date columns : "transaction_date" and "beta_transaction_date" (I am modifying the names for confidentiality).

For my data analysis, date column is one single datum. How do I select a fused date column, transactions, and beta_transaction? (I should get corresponding Null values for either quantity in one of the transaction column for every quantity populated in either beta_transaction or standard transaction)

I considered something around those lines

SELECT date AS d, b_date AS d, transaction, b_transaction FROM transaction

... Could this be the way to go? I can't test right now but I don't think this works.

If my question is nonsense pls excuse me and just delete it, I am a super beginner. I am using Postgres 12 but don't think this is specific to Postgres.

CodePudding user response：

Based on your description, I think this might be what you need:

SELECT [date] as d
    ,  transaction 
FROM   table1
WHERE  [date] is not null
UNION ALL
SELECT b_date as d
    ,  b_transaction
FROM   table1
WHERE  b_date is not null

This has the advantage that if you have a row that has non-null values for both dates, you'll pick up both sets of dates and transaction values.

Another way would be to have just a single SELECT statement, with a COALESCE([date], b_date) or ISNULL([date], b_date) expression, but, if you had non-null values in both date columns, you'd wind up returning the info from the first one listed in the function and ignoring the second. The option with the UNION ALL will return all the data where [date] is non null, and all the data where b_date is non-null.

This may also be able to take advantage of any indexes built over the date fields (for any further sorting or selecting), whereas the results of COALESCE() or ISNULL() would not.