title: Impala SQL build columns based on row data and populating columns with additional row data
body: <p>I'm working in Impala and, while I'm fairly inexperienced in both Impala and SQL, I need to be able to build a data set that looks like the following: </p>
  
  <pre><code>|dayname  | 2017-11-08 00:00:00 | 2017-11-08 01:00:00 | ... |
  |---------|---------------------+---------------------+-----|
  |Wednesday| 20                  | 11                  | ... |
  |---------|---------------------|---------------------|-----|
  |Thursday | 287                 | 17                  | ... |
  |---------|---------------------|---------------------|-----|
  |...      | ...                 | ...                 | ... |
  |---------|---------------------|---------------------|-----|
  </code></pre>
  
  <p>I am unable, due to the constraints of Impala, to use pivot, which would under normal circumstances produce the desired result.</p>
  
  <p>Thus far, I have a SQL SELECT statement which looks like this: </p>
  
  <pre><code>select 
  dayname(date) as dayname,
  utc_hour, 
  sum(case when (`type` IN ('Awesome')) then 1 else 0 end) as some
  FROM (select *, trunc(cast(floor(date / 1000) as timestamp), "HH") as utc_hour
  FROM COOLNESSTYPES
  WHERE date &gt;= 1510082633596 and month &gt;= '2017-11'
  )  a 
  GROUP BY utc_hour, dayname
  ORDER BY utc_hour;
  </code></pre>
  
  <p>and returns the following data:</p>
  
  <pre><code>+-----------+---------------------+-------+
  | dayname   | utc_hour            | some  |
  +-----------+---------------------+-------+
  | Wednesday | 2017-11-08 00:00:00 | 20    |
  | Wednesday | 2017-11-08 01:00:00 | 11    |
  | Wednesday | 2017-11-08 09:00:00 | 1     |
  | Wednesday | 2017-11-08 11:00:00 | 40    |
  | Wednesday | 2017-11-08 12:00:00 | 0     |
  | Wednesday | 2017-11-08 13:00:00 | 6     |
  | Wednesday | 2017-11-08 14:00:00 | 0     |
  | Wednesday | 2017-11-08 16:00:00 | 2     |
  | Wednesday | 2017-11-08 17:00:00 | 10    |
  | Wednesday | 2017-11-08 19:00:00 | 5     |
  | Thursday  | 2017-11-09 07:00:00 | 1     |
  | Thursday  | 2017-11-09 12:00:00 | 0     |
  | Thursday  | 2017-11-09 13:00:00 | 0     |
  | Thursday  | 2017-11-09 14:00:00 | 58    |
  | Friday    | 2017-11-10 09:00:00 | 0     |
  | Friday    | 2017-11-10 10:00:00 | 0     |
  | Friday    | 2017-11-10 16:00:00 | 0     |
  +-----------+---------------------+-------+
  </code></pre>
  
  <p>So, how do I go about doing something like this? On Cloudera's community pages, someone recommends using unions, but I'm not really clear on how I'd label my columns as the row values from my utc_hour column. (see <a href="https://community.cloudera.com/t5/Interactive-Short-cycle-SQL/Transpose-columns-to-rows/td-p/49667" rel="nofollow noreferrer">https://community.cloudera.com/t5/Interactive-Short-cycle-SQL/Transpose-columns-to-rows/td-p/49667</a> for more information on the union suggestion, if needed.) </p>
  
  <p>Any help or ideas on this would be greatly appreciated. Thanks!</p>