title: SQL to normalize existing (many-to-many) data
body: <p><strong>Summary:</strong><br>
  See below for Details. I'm copying the <code>[unanswered</code>] many-to-many question here to the top for readability:</p>
  
  <pre><code>    Given the "Input" table, what is the SQL to generate the 3rd "Output"
      table (Person_plays_Instrument)?
  </code></pre>
  
  <p><br/>
  <br/>
  <strong>Current input (1 table):</strong></p>
  
  <pre><code>OriginalTable:
  PersonId PersonName Instrument_1 Instrument_2 Instrument_3 MailingAddress HomePhone
  --------|----------|------------|------------|------------|--------------|------------
  1        Bob        Violin       Viola        Trumpet      someplace      111-111-1111
  2        Suzie      Cello        Flute        &lt;null&gt;       otherplace     222-222-2222
  3        Jim        Violin       &lt;null&gt;       &lt;null&gt;       thirdplace     333-333-3333
  </code></pre>
  
  <p><strong>Desired output (3 tables):</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <p><strong>Details:</strong></p>
  
  <p>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.</p>
  
  <p>Questions 1 and 2 have been answered, but I am leaving them in in case others find them helpful.</p>
  
  <p><strong>Questions:</strong>  </p>
  
  <p><em>Question #1</em>: <code>[answered</code>]<br>
  How do I generate Person table?</p>
  
  <p><em>Answer #1</em>:<br>
  <a href="https://stackoverflow.com/questions/276737/normalizing-an-existing-ms-access-database/276953#276953">This</a> wonderful post gets me 2/3rds of the way there. For the one-to-many tables, I'm set. Here's the code:</p>
  
  <pre><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;
  </code></pre>
  
  <hr>
  
  <p><em>Question #2</em>: <code>[attempted</code>] (<em>better version by @Branko in Accepted Answer</em>)<br>
  How do I generate Instrument table?</p>
  
  <p><em>Answer #2</em>:<br>
  Again, one-to-many. At first, the multiple columns had me stumped.<br>
  The solution came in two parts:</p>
  
  <ul>
  <li>I'd just have to repeat the INSERT command, once for each column.</li>
  <li>Using <a href="https://stackoverflow.com/a/232951/1421642">this</a> post and the IN operator, I can check each time to confirm I hadn't already inserted that value.</li>
  </ul>
  
  <p>Here's the code:</p>
  
  <pre><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);
  </code></pre>
  
  <hr>
  
  <p><em>Question #3</em>: <code>[unanswered</code>]<br>
  How do I generate Person_plays_Instrument table?</p>