title: Making a Pivot Table in a SubQuery
body: <p>I have four tables in the database that I am trying to pull data from; a, b, c, d.</p>
  <p>For each of these tables I want to pull count(distinct PolicyNumber) and InvestmentCode.</p>
  <p>I would like my output to look something like this: (with 1,2,3,4,5 being the InvestmentCode)</p>
  <pre><code>  a_PolicyCount           b_PolicyCount         c_PolicyCount      d_PolicyCount
  1        14                    56                     32                  74
  2        36                    41                     25                  97
  3        45                    98                     10                   2
  4        12                    16                     13                   5
  5        41                    22                     36                   4
  </code></pre>
  <p>I have tried a couple different ways but I keep getting errors. This is the code I have so far:</p>
  <pre><code>Select
      (Select count(distinct PolicyNumber), InvestmentCode
          from (Select distinct PolicyNumber, Investmentcode
              from a
          ) as a
              group by InvestmentCode
      ) as a,
      (Select count(distinct PolicyNumber), InvestmentCode
          from (Select count(distinct PolicyNumber), InvestmentCode
              from b
              ) as b
              group by InvestmentCode
      ) as b,
      (Select count(distinct PolicyNumber), InvestmentCode
          from (Select distinct PolicyNumber, InvestmentCode
              from c
              ) as c
              group by InvestmentCode
      ) as c,
      (Select count(distinct PolicyNumber), InvestmentCode
          from (Select distinct PolicyNumber, InvestmentCode
              from d
              ) as d
              group by InvestmentCode
      ) as d;
  </code></pre>
  <p>I am just starting to learn SQL so thank you in advance for your answer. I am not sure if it is best to use a Pivot table or by using Joins</p>