title: TSQL Dynamic Pivot function without aggregation
body: <p>I spent a lot of time searching google to find a solution for my problem.
  I have a list like this</p>
  
  <p>It's a result from a query like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT        VBELN, POSNR, PATENT_ID
  FROM            table
  GROUP BY POSNR, VBELN, PATENT_ID
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>  VBELN     POSNR   PATENT_ID
      0090139195  000100  PIST-1
      0090139195  000100  PIST-2
      0090139195  000100  PIST-3
      0090139195  000100  PIST-4
      0090139195  000100  PIST-5
      0090139195  000100  PIST-6
      0090139195  000100  PIST-7
  </code></pre>
  
  <p>One billing position have 1 material and this material can have 0 to around 20 patent_ids.</p>
  
  <p>To compare the combination of Patent_IDs I need to pivot this extract to a list like this</p>
  
  
  
  <pre><code>VBELN       POSNR   Patent 1    Patent 2    Patent 3    Patent 4    Patent 5    Patent 6    Patent 7
  90139195    100     Pist-1      Pist-2      Pist-3      Pist-4      Pist-5      Pist-6      Pist-7
  </code></pre>
  
  <p>In our actual solution we have a very buggy way with 50 (!) views. (not created by myself)
  This views are part of a big stored procedure and the result is wrong.
  I don't want to have a look in all this views and fix it.</p>
  
  <p>So I evaluate the pivot function in TSQL and the idea looks good to me.
  Unfortunately I don't get the result I want...</p>
  
  <p>My result is like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>VBELN       POSNR   Patent 1    Patent 2    Patent 3    Patent 4    Patent 5    Patent 6    Patent 7    
  0090139195  000100  NULL        NULL        NULL        NULL        NULL        NULL        NULL
  </code></pre>
  
  <p>TSQL is</p>
  
  <pre class="lang-sql prettyprint-override"><code>select vbeln, posnr, patent1, patent2, patent3, patent4, patent5, patent6, patent7 
  from
  (
    select  VBELN, POSNR, PATENT_ID, Key_pos
    from tablename
    where VBELN = N'0090139195'
  ) d
  pivot
  (
    min(Key_pos)
    for patent_id in (patent1, patent2, patent3, patent4, patent5, patent6, patent7 )
  ) piv;
  </code></pre>
  
  <p>Where condition is applied for testing purposes only...</p>
  
  <p>Maybe one of you have an idea for me how to fix this stuff.</p>
  
  <p>Thank you very much in advance.</p>