title: using a pivot where the number of columns changes
body: <p>I am using SQL Server 2012 &amp; I have two tables. </p>
  
  <pre><code> tblStocks                     tblFunds
  
   fundCode nvarchar             fundCode nvarchar
   stockID  nvarchar             fundType nvarchar
   shares int
  </code></pre>
  
  <p>I will show an example of the data below before explaining as its probably easier to see an example first.</p>
  
  <pre><code> tblStocks
   fundCode    stockID   shares
   abcd        m33       20
   abcd        b22       10
   abcd        c33       5
   abcd        p99       6
   xyzu        m33       10
   xyzu        b22       8
   xyzu        w88       12
  
   tblFunds
   fundCode   fundType
   abcd       EQ
   xyzu       EQ
  </code></pre>
  
  <p>So please note the funds in tblStocks can have numerous stocks but each will be unique in that fund, i.e. abcd will not hold two m33 stocks. </p>
  
  <p>Also please note abcd and xyzu can hold the same stocks, so both can hold m33.</p>
  
  <p>Below is the result I would like to see. So I have a unique list of stocks and then for each fund I have the number of shares that belong to that fund. So I know that I need to use a pivot here (well its the only way I know). However the added complication (for me) is the number of different funds will not always be two so I don't know how to code this in sql server?</p>
  
  <pre><code>stockID    abcd    xyzu
  m33        20      10
  b22        10      8
  c33        5       0
  p99        6       0
  w88        0       12
  </code></pre>