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: 246695 More actions · Answer 1

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 90252 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: 246695 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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 90252 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: 246695 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 2068 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: 246695 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Steve Collins SSCrazy Points: 2068 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