Insert multiple columns into one column sql

Question

Insert multiple columns into one column sql

Raxso1

SSC Veteran

Points: 270
More actions
August 15, 2019 at 5:22 pm

#3671623

Hi
I have a table with the following table setup
Table A
Name (String)
Surname (Sring)
Address (String)
Employed (String)
Non-Employed (String)
Retired (String)
Table B
Name (String)
Surname (Sring)
Address (String)
Employed Status (String)
I want to insert into table B name, surname, address into there respective columns, but from the 3 columns in Table A for employment, I want to insert into a single Employment Status column in Table B. What is the best way to do this?
Regards
Raxso

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What are the possible values in the three source columns and how do they map to the target column?

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

Raxso1 SSC Veteran Points: 270 More actions · Answer 2

Hi

The values will be the same for each column for example:

Employed (String) - "Manager"

Non-Employed (String) - "Fired"

Retired (String) - "Enjoying Life"

Regards

Rax

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

If you have the following - what do you want to do?

Employed = 'Staff', Non-Employed = 'Not Yet', Retired = 'Soon'

Or...

Employed = 'Soon', Non-Employed = 'Getting Hired', Retired = 'Long ways away'

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Raxso1 SSC Veteran Points: 270 More actions · Answer 4

Hi Jeffrey

The first option would be a good example to use.

Regards

Raxso

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

If the values are, as you suggest:

Employed (String) - "Manager"

Non-Employed (String) - "Fired"

Retired (String) - "Enjoying Life"

what would you set Employed Status to?

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

Raxso1 SSC Veteran Points: 270 More actions · Answer 6

Hi

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.

Regards

Raxso

Raxso1 SSC Veteran Points: 270 More actions · Answer 7

Hi

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.

I just need some examples.

Regards

Raxso

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

I would not recommend that approach - combining discrete data elements into a single column will make it much harder to work with that data.

What is the purpose of combining this data into a single column? How is that going to be utilized?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Raxso1 SSC Veteran Points: 270 More actions · Answer 9

Hi

Can you send me an example on how to add 3 columns to a single column as stated above?

Regards

Raxso

Phil Parkin SSC Guru Points: 246960 More actions · Answer 10

Jeffrey Williams wrote:

I would not recommend that approach - combining discrete data elements into a single column will make it much harder to work with that data.
What is the purpose of combining this data into a single column? How is that going to be utilized?

I agree with this. The query is easy enough to write, but it's not a good idea at all.

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

Raxso1 SSC Veteran Points: 270 More actions · Answer 11

Hi

It will be used in another table where it will be queried, I just need an example of how to insert the 3 columns into a single column. Can you provide me an example?

Regards

Raxso1

Steve Collins SSCrazy Points: 2157 More actions · Answer 12

Do you want concatenation or coalescence of the 3 columns? Can be 1 and only 1 value per row in these 3 columns? Could all 3 columns have values in a single row?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246960 More actions · Answer 13

Steve Collins wrote:

Do you want concatenation or coalescence of the 3 columns? Can be 1 and only 1 value per row in these 3 columns? Could all 3 columns have values in a single row?

This has already been covered:

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.

But I cannot summon the strength to type out the required SQL ... it makes me feel dirty just thinking about it.

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 14

Well it's still not clear to me. Here are examples of both. If you're choosing concat then make sure table_b has wide enough column to fit (including the spaces).

insert table_b(name_str, surname_str, address_str, employed_status)
select
  name_str,
  surname_str,
  address_str,
  concat(employed, ' ', non_employed, ' ', retired)
from
  table_a;

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