SQL PIVOT - Multiple Dynamic Columns
<p><strong>Problem</strong></p>

<p>Need to submit a file to a provider that wishes to use columns for the data if there are multiples.</p>

<p>Our SQL DB logs 'claims' as rows like the examples below I have created:</p>

<pre><code>PolRef@     Number  Type                Date                        Value
ABHX06HQ01  1       Escape Of Water     2009-05-01 00:00:00.000     840
ABHX06HQ01  2       Escape Of Water     2009-05-06 00:00:00.000     400
ABHX06HQ01  3       Escape Of Water     2010-02-01 00:00:00.000     304
ABHX06HQ01  4       Storm Damage        2010-02-11 00:00:00.000     59
ABHX06HQ01  5       Accidental Damage   2011-10-14 00:00:00.000     497
ABHX06HQ01  6       Falling Trees       2011-09-29 00:00:00.000     1172
</code></pre>

<p>The file I need to submit requires each of those claim rows to be columns so essentially the columns in SQL would go:</p>

<pre><code>ClaimNumber1,   ClaimType1,         ClaimDate1,                 ClaimValue1,    ClaimNumber2,   ClaimType2,         ClaimDate2,                 ClaimValue2,    ClaimNumber3,   ClaimType3,         ClaimDate3,                 ClaimValue3
1               Escape Of Water     2009-05-01 00:00:00.000     840             2               Escape Of Water     2009-05-06 00:00:00.000     400             3               Escape Of Water     2010-02-01 00:00:00.000     304
</code></pre>

<p>This can quite possibly be a maximum of 10 although dynamic columns would be nice. I have a feeling this would be a PIVOT, although I have no idea where to even begin.</p>

<p>I started by doing a UNION for each WHERE Number = X but it seems extremely archaic although would eventually get me my result.</p>