Split string into table given row delimiter and column delimiter in SQL server
<p>How to split string containing matrix into table in SQL Server? String has columns and row delimiters. </p>

<p>Suppose I have a string:</p>

<pre><code>declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';
</code></pre>

<p>Expected results (in three separate columns):</p>

<pre><code>+---+---+---+
| A | B | C |
+---+---+---+
| D | E | F |
+---+---+---+
| X | Y | Z |
+---+---+---+
</code></pre>

<p>I am looking for general solution which has not defined number of columns and rows. So the string:</p>

<pre><code>declare @str varchar(max)='A,B;D,E';
</code></pre>

<p>will be split into table with two columns:</p>

<pre><code>+---+---+
| A | B |
+---+---+
| D | E |
+---+---+
</code></pre>

<p><strong>My efforts.</strong> My first idea was to use dynamic SQL which turns the string into:
<code>insert into dbo.temp values (...)</code> This approach although very fast has a minor drawback because it requires creating a table with the right number of columns first. I have presented this method in <a href="https://stackoverflow.com/a/39758478/1903793"><strong>the answer to my own question below</strong></a> just to keep the question short. </p>

<p>Another idea would be to write down the string to a CSV file on the server and then <code>bulk insert</code> from it. Though I do not know how to do it and what would be performance of first and second idea. </p>

<p>The reason why I asked the question is because I want to import data from Excel to SQL Server. As I have experimented with different ADO approaches, this method of sending matrix-string is a landslide victory, especially when the length of the string increases. I asked a younger twin brother of the question here: <strong><a href="https://stackoverflow.com/questions/39597999/turn-excel-range-into-vba-string">Turn Excel range into VBA string</a></strong>  where you will find suggestions how to prepare such a string from Excel range.</p>

<p><strong>Bounty</strong> I decided to award <strong>Matt</strong>. I weighed highly <strong>Sean Lange's</strong>  answer. Thank you Sean. I liked Matt's answer for its simplicity and shortness. Different approaches apart from Matt's and Sean's could be in parallel use so for the time being I am not accepting any answer (update: Finally, after a few months, I have accepted Matt's answer). I wish to thank <strong>Ahmed Saeed</strong> for his idea with VALUES, for it is a nice evolution of the answer I began with. Of course, it is no match for the Matt's or Sean's. I upvoted every answer. I will appreciate any feedback from you on using these methods. Thank you for the quest. </p>