August 15, 2019 at 11:59 pm
Well it's still not clear to me.
Really?
How is COALESCE ever going to take ('Manager', 'Fired', 'Enjoying Life') as its arguments and return 'Manager Fired Enjoying Life'?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 16, 2019 at 2:37 am
The first post referenced:
The employment status depending on the Name would be "Manager", "Fired", "Enjoying Life". I basically need to insert 3 different columns into a single column.
Then you wrote:
How is COALESCE ever going to take ('Manager', 'Fired', 'Enjoying Life') as its arguments and return 'Manager Fired Enjoying Life'?
How was I supposed to know "Manager", "Fired", "Enjoying Life" was 1 set with 3 elements and not 3 sets with 1 element (each)? How can a person be employed, not employed, and retired at the same time? Just based on the column names it seemed possible the values could be mutually exclusive. I'm also thinking the data model seems strange. Idk hopefully the OP now has the example they need.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 16, 2019 at 7:09 am
Hi
The 2nd table will only have 1 entry for the person employment status from the first table whether it would be "Manager", "Fired" or "Enjoy Life". It will not contain all 3 columns in one. Do you have an example to do this?
Regards
raxso
August 16, 2019 at 11:58 am
The "or" we were looking for. The second example, the one with "coalesce", consolidates the 3 columns (Employed, Not employed, Retired) into 1 column (Employed status) by selecting the first (left-to-right) non-null value from the column list.
Disclaimer: please make sure the 3 columns in table 1 are mutually exclusive. Design-wise, table 1 doesn't follow "3rd normal form" best practice, so producing expected results from queries could be very, very tricky.
insert table_b(name_str, surname_str, address_str, employed_status)
select
name_str,
surname_str,
address_str,
coalesce(employed, non_employed, retired)
from
table_a;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 16, 2019 at 2:39 pm
Hi
The 2nd table will only have 1 entry for the person employment status from the first table whether it would be "Manager", "Fired" or "Enjoy Life". It will not contain all 3 columns in one. Do you have an example to do this?
Regards
raxso
The next time you ask a question, please supply sample source data and desired results based on that source data, as your English-language description of requirements is completely befuddling.
This problem would have been solved in one post had you done this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 16, 2019 at 2:46 pm
case statement
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply