title: JOIN with multiple columns in postgresql
body: <p>I have the following two tables in postgresql:</p>
  
  <pre><code>     TABLE: act_codes
      ===================
       activity  act_desc
      ____________________
          1      sleeping
          2      commuting
          3      eating
          4      working
       TABLE: data
      ===================
      act1_1     act_1_2     act1_3     act1_4
      ---------------------------------------------
        1         1           3           4
        1         2           2           3
        1         1           2           2
        1         2           2           3
        1         1           1           2
        1         1           3           4
        1         2           2           4
        1         1           1           3
        1         3           3           4
        1         1           4           4
  </code></pre>
  
  <p>The act_codes table is basically a table of activities (with a code and a description), and the data table contains the activity codes for (in this case) 4 different times (act1_1, act1_2, act1_3 and act1_4).</p>
  
  <p>I am trying to query this to get a table of counts for each activity.  I have managed to do this for each individual column (in this case act1_4) like this:</p>
  
  <pre><code>    SELECT A.act_code, A.act_desc, COUNT (act1_4) 
      FROM act_codes AS A
      LEFT JOIN data AS D 
      ON D.act1_4 = A.act_code
      GROUP BY A.act_code, A.act_desc;   
  </code></pre>
  
  <p>Which works fine for that column, but I have a very large number of columns to work through, so would prefer it if there was a way to do this within an SQL query.</p>
  
  <hr>
  
  <p>I now have the following query (many thanks to banazs): </p>
  
  <pre><code>    SELECT
          ac.act_code, 
          ac.act_desc,
          act_time,
          COUNT(activity) AS act_count
      FROM
          (SELECT
              UNNEST(array['act1_1','act1_2','act1_3','act1_4']) AS act_time,
              UNNEST(array[d.act1_1, d.act1_2, d.act1_3, d.act1_4]) AS activity
          FROM
              data d) t
      RIGHT JOIN
          act_codes ac ON t.activity = ac.act_code
      GROUP BY
          ac.act_code, 
          ac.act_desc,
          act_time, activity
      ORDER BY 
          activity, 
          act_time
      ;
  </code></pre>
  
  <p>Which outputs:</p>
  
  <pre><code>    act_code        act_desc        act_time        act_count
      ---------------------------------------------------------
          1           sleeping            act1_1          10
          1           sleeping            act1_2          6
          1           sleeping            act1_3          2
          2           commuting           act1_2          3
          2           commuting           act1_3          4
          2           commuting           act1_4          2
          3           eating              act1_2          1
          3           eating              act1_3          3
          3           eating              act1_4          3
          4           working             act1_3          1
          4           working             act1_4          5
  </code></pre>
  
  <p>Which is basically what I was looking for.  Ideally, the rows with zero counts could be added in somehow, but gI am guessing that this is perhaps best done as a separate process (e.g. constructing a crosstab in R or something).</p>