title: Crosstab in PostgreSQL - How to show aggregates by year in columns?
body: <p>I'm new to SQL. I have unioned several tables together where data is stored separately by year. I am attempting to count the number of rows per unique ID by year. My current query is:</p>
  <pre><code>SELECT start_station_id,
  date_part('year',start_time),
  COUNT(*)
  FROM public.capitalbikeshare_2016
  WHERE user_type = 'Casual'
  GROUP BY 1, 2
  UNION ALL
  SELECT start_station_id,
  date_part('year',start_time),
  COUNT(*)
  FROM public.capitalbikeshare_2017
  WHERE user_type = 'Casual'
  GROUP BY 1, 2
  UNION ALL
  ...
  </code></pre>
  <p>and outputs as:</p>
  <pre><code>unique id | date_part | count
  id1          2016        #
  id1          2017        #
  id1          2018        #
  id1          2019        #
  id2          2016        #
  ...
  </code></pre>
  <p>I have tried count if/filters but have been unsuccessful at transposing the years to columns. I'm hoping to transpose them to columns so that I can then calculate the year over year growth.</p>