-2

I have a table with a bunch of columns like this:

ID, Name1, Name2, Name3, Title1, Title2, Title3, Email1, Email2, Email3

1, joe blow, Jane doe, john doe, president, secretary, janitor, no@no.com, yes@no.com,maybe@no.com

I am trying to split into multiple rows and insert into a new table with these column headers:

ID, Name, Title, Email

*1 - joe blow, president, no@no.com

1 - Jane doe, secretary, yes@no.com

1 - John Doe, janitor, maybe@no.com*

I cant find any info online that can help me out with this one. I have over 4K entries in this table alone so a manual entry is out of the question.

The ID has to be the same for each company. meaning all one row is one ID, the next row is another ID, etc.

Raidri
  • 17,258
  • 9
  • 62
  • 65
madscientest
  • 1
  • 4
  • Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. – Taryn Sep 26 '13 at 23:53
  • How is this related to C#? – Jeroen Vannevel Sep 26 '13 at 23:55
  • SQL SERVER, i took out the C# Jeroen, i was using it on this project for something else. sorry for the confusion. – madscientest Sep 27 '13 at 00:00
  • I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders Sep 27 '13 at 00:08

2 Answers2

2

You did not specify what version of SQL Server you are using but this process of converting multiple columns into multiple rows is called UNPIVOT. There are several ways that you can UNPIVOT the data in SQL Server.

If you are using SQL Server 2008+, then you can use CROSS APPLY with a VALUES clause to unpivot the columns in the sets of 3:

select id, name, title, email
from yourtable
cross apply
(
  values
    (name1, title1, email1),
    (name2, title2, email2),
    (name3, title3, email3)
) c (name, title, email);

See SQL Fiddle with demo

If you are using SQL Server 2005+, you can use CROSS APPLY with a UNION ALL:

select id, name, title, email
from yourtable
cross apply
(
  select name1, title1, email1 union all
  select name2, title2, email2 union all
  select name3, title3, email3
) c (name, title, email);

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
0

Any issues with unions? 

select ID, Name1, Title1, Email1
from table
union all
select ID, Name2, Title2, Email2
from table
union all
select ID, Name3, Title3, Email3
from table

Are all columns always populated? You can use a where clause to ensure name3 is populated (where name3 is not null) to make sure all your values in the new table are populated.

Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • Apart from 3 table scans and the sorting/merging caused by UNION? – Panagiotis Kanavos Sep 27 '13 at 11:34
  • No, not all columns are always populated. Also I want these in a new table as I'm trying to fix a preexisting design flaw. – madscientest Sep 27 '13 at 15:12
  • In any of those union statements, you can have a series of where clauses to format or filter out bad data. I'd use the solution from bluefeet if you are in sql server 2008+...otherwise this union. @Panagiotis - Yes, not very efficient. 4k rows in a table, 3 table scans is not bad. – Twelfth Sep 27 '13 at 16:22