SQL multiple rows with same row names to column
<p>I have the following data in SQL (nvarchar, nvarchar)</p>

<pre><code>Name: Test Person 
Phone Number: 290831283
Fax Number: 192389182 
Email Address: test@test.com 
Name: Abacus Testing 
Phone Number: 901823908 
Fax Number: 9213989182 
Email Address: abacus@test.com
</code></pre>

<p>How can I format this data to be:</p>

<pre><code>[Name] [Phone Number] [Fax Number] [Email Address]
Test Person 290831283 192389182 test@test.com 
Abacus Testing 901823908 9213989182 abacus@test.com
</code></pre>

<p>So basically setting the rows into matching column</p>

<p>I was hoping to use a Pivot table, but I only get the first row due to aggregation.</p>

<pre><code>SELECT [Name], [Phone Number], [Fax Number], [Email Address]
FROM
(
    SELECT 
            ColumnName,
            Data
    FROM 
            TheData
) SRC
PIVOT
(
  MAX(Data)
  FOR ColumnName IN ([Name], [Phone Number], [Fax Number], [Email Address])
) PIV
</code></pre>

<p>I would prefer no CURSOR methods, any alternatives?</p>

<ul>
<li>Edit: added other fields available for use</li>
</ul>

<p>The other fields available are Label Id for each entry. i.e Name=1, PhoneNumber=2, FaxNumber=3, EmailAddress=4</p>

<p>The common Id for each of the 4 entries is also available. i.e first 4 entries have id 1001, second 4 entries have id 1002</p>