CODEWITHSUNDEEP
sql-serverdata-structurescombinations
David Flynn
David Flynn

Reputation: 105

Combine two or more columns into one in a table

I have an existing database with a table that I need to alter. Due to some recent changes requested, I need to take two columns out of a table and put the data from those two columns into an existing column in the table. Thankfully, they are all Varchar type so the data doesn't have to be transformed.

Here's what the table looks like now: enter image description here

And here's what I want it to be:

enter image description here

I'm not exactly sure where to even start with this. So what's the best way to do this without breaking the table or losing any data?

NOTE: This is NOT for querying. I want to change the structure of the table itself to remove these two columns but take their data and dump it into an existing column.

Upvotes: 0

Views: 537

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

This can be done by a simple update statement:

Update table
Set colC = colC + char(13) + char(10) + colD +char(13) + char(10) + colE

Note that char(13) + char(10) is a line break.

After you verify that the data was copied, use alter table to drop colD and colE.

The question is are you sure this move is wise?

This move seem to contredict a basic rule of relational databases design. If these columns stores different data entities I would strongly recommend against such a move.
Unless these values actually needs to be merged into a single value (on the logical level) then this is the wrong thing to do.

Upvotes: 3

Related Questions