title: Show all varchar values with PIVOT
body: <p>I need to show all varchar values with pivot but I'm not sure if that is possible?</p>
  <p>I have <code>OCCUPATIONS</code> table:</p>
  <pre class="lang-sql prettyprint-override"><code>-----------+------------+
  |   NAME    | OCCUPATION |
  +-----------+------------+
  | Eve       | Actor      |
  | Jennifer  | Actor      |
  | Ketty     | Actor      |
  | Samantha  | Actor      |
  | Aamina    | Doctor     |
  | Julia     | Doctor     |
  | Priya     | Doctor     |
  | Ashley    | Professor  |
  | Belvet    | Professor  |
  | Britney   | Professor  |
  | Maria     | Professor  |
  | Meera     | Professor  |
  | Naomi     | Professor  |
  | Priyanka  | Professor  |
  | Christeen | Singer     |
  | Jane      | Singer     |
  | Jenny     | Singer     |
  | Kristeen  | Singer     |
  +-----------+------------+
  </code></pre>
  <p>Pivot query:</p>
  <pre class="lang-sql prettyprint-override"><code>    select NAME, OCCUPATION from OCCUPATIONS 
      ) t
      PIVOT( MAX(NAME) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])
      ) as PIVOT_TABLE;
  </code></pre>
  <p>Query result:</p>
  <pre class="lang-sql prettyprint-override"><code>+--------+-----------+----------+----------+
  | Doctor | Professor |  Singer  |  Actor   |
  +--------+-----------+----------+----------+
  | Priya  | Priyanka  | Kristeen | Samantha |
  +--------+-----------+----------+----------+
  </code></pre>
  <p>Above query gives only 1 record in each column but I want to get all.</p>