SQL query from flat file

Question

I have a table such as:

John email1 email2 email3 ...and so on

I need to transform the dataset to this format:

John email1
John email2
John email3

it could be exported out to Excel and the imported back in if that would be easier. Thanks...

Answer 1

What do you mean fix? You could just do something like this:

SELECT name+' '+email1 as email1,
       name+' '+email2 as email2, 
       name+' '+email3 as email3, 
       name+' '+email3 as email3, 
       ...
FROM table

The above was written before I saw the formating ... this I believe is what you want -- only tested on mssqlserver

declare @t table
(
  name varchar(max),
  email1 varchar(max),
  email2 varchar(max),
  email3 varchar(max),
  email4 varchar(max)
)

insert into @t 
 values ('name1a','email1a','email2a','email3a','email4a')
insert into @t  
 values ('name2b','email1b','email2b','email3b','email4b')

 select * from @t

SELECT name, email 
FROM
(
SELECT name, email1, email2, email3, email4
FROM @t) p
UNPIVOT
   (email FOR emails IN 
      (email1, email2, email3, email4)
)AS unpvt
  

Answer 2

I see no reasons to "go out" to XLS or something else and then re-importing.

You can fix the situation by

1) (only if deemed worthy, depending on DB size, number of rows etc.), possibly drop a few indexes on the table.

2) Running INSERT queries to add the new rows, i.e. these made from the Name and the "Emailn" columns (along with other desired columns or desired default values)

3) Once all the "Emailn" columns have been dispatched, Altering the table schema to drop these columns.

4) Re-building the indexes dropped earlier and/or re-packing the others.

Specifically, for #2, the query(ies) would look like

INSERT INTO MyTable 
   (Name, Email1, SomeOtherColumn, YetOtherColumn)
   SELECT Name, Email2, someColumn, "ABC"
   FROM MyTable
   WHERE Email2 IS NOT NULL

You do this for each "Emailn" column beyond Email1 of course. Et voila...

Only possibly difficulties when the table is very big etc, but since you were considering Excel, that's probably not the case.

Answer 3

Assuming that Name, Email1, Email2, Email3 are the column names:

   Select Name, Email1
    From YourTable
    Where Email1 <> ''
    Union
    Select Name, Email2
    From YourTable
    Where Email2 <> ''
    Union
    Select Name, Email3
    From YourTable
    Where Email3 <> ''

Answer 4

Complete overhaul (My original answer was based on a short-lived incorrect version of the question).

If "flat file" refers to a text file, then a simple (non-SQL) version for the conversion would be to use awk with something such as this:

awk "{ for (i = 2; i <= NF; i++ ) { print $1, $i }}" < original.txt