SQL to normalize existing (many-to-many) data

Question

Summary:
See below for Details. I'm copying the [unanswered] many-to-many question here to the top for readability:

    Given the "Input" table, what is the SQL to generate the 3rd "Output"
    table (Person_plays_Instrument)?

Current input (1 table):

OriginalTable:
PersonId PersonName Instrument_1 Instrument_2 Instrument_3 MailingAddress HomePhone
--------|----------|------------|------------|------------|--------------|------------
1        Bob        Violin       Viola        Trumpet      someplace      111-111-1111
2        Suzie      Cello        Flute        <null>       otherplace     222-222-2222
3        Jim        Violin       <null>       <null>       thirdplace     333-333-3333

Desired output (3 tables):

Person:
Id Name   MailingAddress HomePhone
--|------|--------------|------------
1  Bob    someplace      111-111-1111
2  Suzie  otherplace     222-222-2222
3  Jim    thirdplace     333-333-3333

Instrument:
Id Name
--|-------
1  Violin
2  Cello
3  Viola
4  Flute
5  Trumpet

Person_plays_Instrument:
PersonId InstrumentId
--------|------------
1        1
1        3
1        5
2        2
2        4
3        1

---

Details:

I have a single flat SQL table which started out as a spreadsheet. I'd like to normalize it. I'll split this into 1 question for each table.

Questions 1 and 2 have been answered, but I am leaving them in in case others find them helpful.

Questions:

Question #1: [answered]
How do I generate Person table?

Answer #1:
This wonderful post gets me 2/3rds of the way there. For the one-to-many tables, I'm set. Here's the code:

[add autonumber field to OriginalTable, name it PersonId]
[create empty Person table with Id, Name, MailingAddress, HomePhone fields]

INSERT INTO Person (Id, Name, MailingAddress, HomePhone)
  SELECT o.PersonID, o.PersonName, o.MailingAddress, o.HomePhone
  FROM OriginalTable as o
  WHERE o.PersonName Is Not Null;

---

Question #2: [attempted] (better version by @Branko in Accepted Answer)
How do I generate Instrument table?

Answer #2:
Again, one-to-many. At first, the multiple columns had me stumped.
The solution came in two parts:

• I'd just have to repeat the INSERT command, once for each column.
• Using this post and the IN operator, I can check each time to confirm I hadn't already inserted that value.

Here's the code:

[create empty Instrument table with Id[autonumber], Name fields]

INSERT INTO Instrument (Name)
  SELECT Distinct o.Instrument_1
  FROM OriginalTable as o
  WHERE o.Instrument_1 Is Not Null
  AND o.Instrument_1 Not In (SELECT Name from Instrument);

INSERT INTO Instrument (Name)
  SELECT Distinct o.Instrument_2
  FROM OriginalTable as o
  WHERE o.Instrument_2 Is Not Null
  AND o.Instrument_2 Not In (SELECT Name from Instrument);

INSERT INTO Instrument (Name)
  SELECT Distinct o.Instrument_3
  FROM OriginalTable as o
  WHERE o.Instrument_3 Is Not Null
  AND o.Instrument_3 Not In (SELECT Name from Instrument);

---

Question #3: [unanswered]
How do I generate Person_plays_Instrument table?

Answer 1

Assuming there is OriginalTable.PersonID, which you haven't shown us, but is implied by your own answer #1, the answer #3 can be expressed simply as:

INSERT INTO Person_plays_Instrument (PersonId, InstrumentId)
SELECT PersonID, Instrument.Id
FROM
    OriginalTable
    JOIN Instrument
        ON OriginalTable.Instrument_1 = Instrument.Name
        OR OriginalTable.Instrument_2 = Instrument.Name
        OR OriginalTable.Instrument_3 = Instrument.Name;

BTW, there is a more concise way to express the answer #2:

INSERT INTO Instrument (Name)
    SELECT *
    FROM (
        SELECT o.Instrument_1 I
        FROM OriginalTable as o
        UNION
        SELECT o.Instrument_2
        FROM OriginalTable as o
        UNION
        SELECT o.Instrument_3
        FROM OriginalTable as o
    ) Q
    WHERE I IS NOT NULL;

And here is a fully working SQL Fiddle example for MS SQL Server. Other DBMSes should behave similarly. BTW, you should tag your question appropriately to indicate your DBMS.