title: Dynamic SQL UNPIVOT and Select INTO temporary table
body: <p>I know that there are a few questions out there on Dynamic SQL and Insert into temporary tables, but I cannot find an exact match for my particular problem. <br><br> I have data in 52 columns in ##tmp and I need to total these and store in ##tmp2.<br><br> Note: The syntax works fine if I remove the first line <br><br></p>
  
  <pre><code> select * into ##tmp2 from
  </code></pre>
  
  <p>It's the "Select Into" that I have a problem with! My current Dynamic query provides the following syntax, but I cannot get this to work. Currently the open bracket after the initial "from" has no matching closing bracket. <br><br>I've tried various positions for the final ")" but get a mixture of<br><br></p>
  
  <pre><code>Incorrect syntax near ')' -- if placed at the end of the statement
  Invalid column name 'wk' -- if added as  "from ##tmp) onto the second from"
  Incorrect syntax near the keyword 'group'. -- if added after ")) as U"
  </code></pre>
  
  <p>Here's the current syntax</p>
  
  <pre><code>select * into ##tmp2 from(
  select x,y,sum(wk) as mysum  from ##tmp
    unpivot (wk for nwk in ([1],[2],[3],[4],[7],[8],[9],[10],[11],[12],[13],   [14],[15],[16],
  [17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],
  [35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52]))
   as u  group by x,y
  </code></pre>
  
  <p>Any ideas?</p>