CODEWITHSUNDEEP
sqlsql-server
user3009908
user3009908

Reputation: 25

How can I combine multiple columns of the same data into a single column?

I have an issue here that is arising from poor data formatting (not on my behalf). I had a large CSV file downloaded from an external entity with nation wide data - it has about 5,000,000+ rows so that its too large of a file to open, let alone manually manipulate the data. I did get it uploaded to our SQL database, but getting the data into a usable format is difficult; each row has 10 different category codes, and can have multiple codes in each category. Unfortunately, they added new columns to handle this instead of adding a new row. Its tough to describe without an example:

ID  A_Code1 A_Code2 A_Code3 B_Code1 B_Code2 B_Code3
1   123     765     654     qwe     asd     zxc
2   987     345     567     poi     lkj     mnb

and this is what I need:

ID  A_Code  B_Code
1   123     qwe
1   765     asd
1   654     zxc
2   987     poi
2   345     lkj
2   567     mnb

The way it is set up now makes querying nearly impossible as there are about 10 different types of on each row, and there are 10 columns for each code type. This means I have to query 100 different columns when I should only have to query 10.

If somebody knows a way to do this, it would be greatly appreciated. I have not been able to find anything like this so far, so I am getting desperate!

Thank you!

Upvotes: 2

Views: 2317

Answers (1)

Taryn
Taryn

Reputation: 247860

You need to unpivot the multiple columns of data into multiple rows, depending on your version of SQL Server there are several ways to get the result.

You can use CROSS APPLY and UNION ALL if using SQL Server 2005+:

select id, A_Code, B_Code
from yourtable
cross apply
(
  select A_Code1, B_Code1 union all
  select A_Code2, B_Code2 union all
  select A_Code3, B_Code3
) c (A_Code, B_Code);

See SQL Fiddle with Demo.

You can also use CROSS APPLY with VALUES if using SQL Server 2008+:

select id, A_Code, B_Code
from yourtable
cross apply
(
  values
    (A_Code1, B_Code1),
    (A_Code2, B_Code2),
    (A_Code3, B_Code3)
) c (A_Code, B_Code);

See SQL Fiddle with Demo.

This allows you to convert the columns into rows in pairs - meaning A_Code1 and B_Code1 will be matched in the final result.

You could also use a UNION ALL:

select id, A_Code = A_Code1, B_Code = B_Code1
from yourtable 
union all
select id, A_Code = A_Code2, B_Code = B_Code2
from yourtable 
union all
select id, A_Code = A_Code3, B_Code = B_Code3
from yourtable ;

See SQL Fiddle with Demo

Upvotes: 3

Related Questions