CODEWITHSUNDEEP
sqlsql-serversql-updatesql-insert
LuckySlevin
LuckySlevin

Reputation: 745

How to append two columns into one column in SQL?

I have two columns in my Table called as Workers - Technicians. In Workers I have name of workers, and in Technicians I have name of technicians who is working in company. Here is how it looks:

Workers                              Technicians

Andy                  Kevin
Conan                 Jason
Jay                   Ray
Donald                Daryl
Martin                .
Mark                  .(rows goes on)
.
.(rows goes on)

What I want to do is to append these rows and having a column called Employees. Here what i want:

Employees

Andy                  
Conan                 
Jay                   
Donald               
Martin                
Mark
Kevin
Jason
Ray
Daryl
.
.

I don't want to create another table, I just want to add a new column. How would i do this?

Upvotes: 6

Views: 14053

Answers (4)

Martin Taleski
Martin Taleski

Reputation: 6448

An union statement should do the job here. Something like this:

 select Name from Workers-Technitians where Worker not null
 UNION 
 select Name from Workers-Technitians where Technitian not null

Keep in mind that both queries must have an equal column count when using an union

Upvotes: 11

phatfingers
phatfingers

Reputation: 10250

Assuming your table is named "staff", you could do something like this.

select distinct employees from (
  select workers as employees from staff s1
  union select technicians as employees from staff s2
) as emps

Perhaps you need to take another look at your design, though. Adding a third column won't work out for you because the first two columns represent two rows. In fact, there already appears to be a forced relationship between the workers and technicians-- they seem to be bundled together in the same record for no particular reason.

Consider starting fresh with an Employee table, with one employee per row, and abandon the existing table. You can maintain employee attributes in that table if the requirements are simple, or join a separate table of attributes for more complex cases. In either case, normalizing your data will make your life easier and use of a union to join columns will be unnecessary.

Upvotes: 8

ssarabando
ssarabando

Reputation: 3517

I also don't understand fully what you want to do (mainly because of this: if, say, a row has "Andy" in Workers and "Kevin" in Technicians, what will you put in Employees? Andy or Kevin?), but to add a new column to the table and then fill it with data you need to:

  1. Add it (assuming your table is named Table and 100 chars are enough for the new column):

    alter table [Table] add [Employees] nvarchar(100) null

  2. Fill it:

    update [Table] set [Employees] = [Workers]

    -- or:

    update [Table] set [Employees] = [Technicians]

I believe what you really want is a new table, Employees, and to change the Workers and Technicians into relationships drawn from it. For that, see the link Simon gave you about database normalization.

Upvotes: 1

Simon
Simon

Reputation: 6152

I don't entirely understand your question but you don't want to add an additional column, this introduces (more) data redundancy and will make any future updates or querying more difficult. Use one of the SELECT query solutions given and seriously consider re-factoring your two existing separate columns into a single column. I suggest you take a look at database normalisation if you're not already familiar with the concept.

Upvotes: 1

Related Questions