title: Using string_split to create rows from multiple columns
body: <p>I have data that looks something like this example (on an unfortunately much larger scale):</p>
  
  <pre><code>+----+-------+--------------------+-----------------------------------------------+
  | ID | Data  | Cost               | Comments                                      |
  +----+-------+--------------------+-----------------------------------------------+
  | 1  | 1|2|3 | $0.00|$3.17|$42.42 | test test||previous thing has a blank comment |
  +----+-------+--------------------+-----------------------------------------------+
  | 2  | 1     | $420.69            | test                                          |
  +----+-------+--------------------+-----------------------------------------------+
  | 3  | 1|2   | $3.50|$4.20        | |test                                         |
  +----+-------+--------------------+-----------------------------------------------+
  </code></pre>
  
  <p>Some of the columns in the table I have are pipeline delimited, but they are consistent by each row. So each delimited value corresponds to the same index in the other columns of the same row.</p>
  
  <p>So I can do something like this which is what I want for a single column:</p>
  
  <pre><code>SELECT ID, s.value AS datavalue
  FROM MyTable t CROSS APPLY STRING_SPLIT(t.Data, '|') s
  </code></pre>
  
  <p>and that would give me this:</p>
  
  <pre><code>+----+-----------+
  | ID | datavalue |
  +----+-----------+
  | 1  | 1         |
  +----+-----------+
  | 1  | 2         |
  +----+-----------+
  | 1  | 3         |
  +----+-----------+
  | 2  | 1         |
  +----+-----------+
  | 3  | 1         |
  +----+-----------+
  | 3  | 2         |
  +----+-----------+
  </code></pre>
  
  <p>but I also want to get the other columns as well (cost and comments in this example) so that the corresponding items are all in the same row like this:</p>
  
  <pre><code>+----+-----------+-----------+------------------------------------+
  | ID | datavalue | costvalue | commentvalue                       |
  +----+-----------+-----------+------------------------------------+
  | 1  | 1         | $0.00     | test test                          |
  +----+-----------+-----------+------------------------------------+
  | 1  | 2         | $3.17     |                                    |
  +----+-----------+-----------+------------------------------------+
  | 1  | 3         | $42.42    | previous thing has a blank comment |
  +----+-----------+-----------+------------------------------------+
  | 2  | 1         | $420.69   | test                               |
  +----+-----------+-----------+------------------------------------+
  | 3  | 1         | $3.50     |                                    |
  +----+-----------+-----------+------------------------------------+
  | 3  | 2         | $4.20     | test                               |
  +----+-----------+-----------+------------------------------------+
  </code></pre>
  
  <p>I'm not sure what the best or most simple way to achieve this would be</p>