title: Dynamically Create SQL Columns using Table Rows
body: <p>I have a extract that looks someting like this</p>
  <pre><code>| ID |   NAME   | ATTRIBUTE |  VALUE  |
  |  1 |  PENCIL  | TYPE      |  HB2    |
  |  1 |  PENCIL  | COLOR     |  RED    |
  |  1 |  PENCIL  | MADE IN   | JAPAN   |
  |  1 |  PENCIL  | HAS ERASER|  YES    |
  |  2 |  LIGHT   | WATTS     |  60     |
  |  2 |  LIGHT   | COLOR     |  WHITE  |
  |  3 |  BOOK    | NAME      |  HELLO  |
  |  3 |  BOOK    | WEIGHT    |  200G   |
  |  3 |  BOOK    | ISBN      |  901551 |
  </code></pre>
  <p>I need assistance with a loop that would Count the max number of Attributes for an ID, in this example Pecil has 4 Attributes, and to then Create a table that has ID, Name, Attribute1, Value1, Attribute2, Value2, Attribute3, Value3, Attribute4, Value4.</p>
  <p>If a product only has 2 Attributes, then Attributes and Values 3 and 4 would be blank.</p>
  <pre><code>| ID |   NAME   | ATTRIBUTE1 |  VALUE1  | ATTRIBUTE2 |  VALUE2  | ATTRIBUTE3 |  VALUE3  |
  |  1 |  PENCIL  | TYPE       |  HB2     | COLOR      |  RED     |  MADE IN   |  JAPAN   | ...etc
  |  2 |  LIGHT   | WATTS      |  60      | COLOR      |  WHITE   |            |          |
  |  3 |  BOOK    | NAME       |  HELLO   | WEIGHT     |  200G    | ISBN       |  901551  |
  </code></pre>
  <p>I am still trying to dynamically add the columns.</p>
  <pre><code>DECLARE @RunningTotal BIGINT = 1;
  DECLARE @MAXAttributeCnt BIGINT = (SELECT MAX(cnt) FROM (SELECT ID,count(1) as cnt FROM Table1 group by ID ) x);
  
  
  WHILE @RunningTotal &lt;= @MAXAttributeCnt
  
  BEGIN
          DECLARE @SQL NVARCHAR(MAX)
          SET @SQL = 'ALTER TABLE Table1
                      ADD ATTRIBUTE' + CAST(@RunningTotal as varchar(10)) + ' BIGINT NULL'
  
          
          EXECUTE (@SQL)
          SET @RunningTotal = @RunningTotal + 1    
  END
  </code></pre>
  <p>But that is not working. I get a few loops of</p>
  <pre><code>&gt; Could not find stored procedure 'ALTER TABLE Table1 ADD ATTRIBUTE1 BIGINT NULL'.
  </code></pre>
  <p>Once the Table is built, I would need a similar loop to populate the Fields.
  Any assistance would be appreciated.</p>
  <p><strong>UPDATE: I dont know if updating the OP is the accepted practice here. Please correct me if there is a different method.</strong></p>
  <p>Thank you for the assistance below, wrapping @SQL in () has worked. I now have a table with 4 Attribute and 4 Value Columns added, all dynamically named. However - I still need to populate it with the Attributes and Values. Anyone have an idea of how that would work ? This is what I now have, All nulls up to VALUE4.</p>
  <pre><code>| ID |   NAME   | ATTRIBUTE |  VALUE  | ATTRIBUTE1 | VALUE1 | ATTRIBUTE2 |...
  |  1 |  PENCIL  | TYPE      |  HB2    |    NULL    |  NULL  | NULL       |...
  |  1 |  PENCIL  | COLOR     |  RED    |    NULL    |  NULL  | NULL       |...
  |  1 |  PENCIL  | MADE IN   | JAPAN   |    NULL    |  NULL  | NULL       |...
  |  1 |  PENCIL  | HAS ERASER|  YES    |    NULL    |  NULL  | NULL       |...
  |  2 |  LIGHT   | WATTS     |  60     |    NULL    |  NULL  | NULL       |...
  |  2 |  LIGHT   | COLOR     |  WHITE  |    NULL    |  NULL  | NULL       |...
  |  3 |  BOOK    | NAME      |  HELLO  |    NULL    |  NULL  | NULL       |...
  |  3 |  BOOK    | WEIGHT    |  200G   |    NULL    |  NULL  | NULL       |...
  |  3 |  BOOK    | ISBN      |  901551 |    NULL    |  NULL  | NULL       |...
  </code></pre>