SQLite pivot producing alternate NULLS

Question

I'm having a problem with my SQLite pivot code, mainly taken from McPeppr's answer here: Pivot in SQLite

Creating my temp table:

WITH t1 AS (
SELECT      band, 
    p.name, 
    status, 
    strftime('%Y-%m', time_start) AS Month,
    AVG(time) AS Avg
FROM person p

JOIN action a ON p.person_id = a.person_id
JOIN log l ON p.log_id = l.log_id

WHERE p.person = 'Joe' AND opps = '2'

GROUP BY band, Month, status, strftime('%Y-%m', time_stamp_start)

ORDER BY Month, CASE status
                WHEN 'one' THEN 0
                WHEN 'two' THEN 1
                WHEN 'three' THEN 2
                WHEN 'four' THEN 3
            END
),

t1 looks like:

  band |  name  | status |   month   |     AVG 
 ------+--------+--------+-----------+---------------
    1  |  Joe   |  one   |  2018-01  |     3.33      
    2  |  Joe   |  one   |  2018-01  |     4.11
    1  |  Joe   |  two   |  2018-02  |     2.55      
    2  |  Joe   |  two   |  2018-02  |     3.45 
  ..........

When I try pivot in a select I get:

Select band, Month,
case when status = 'one' then response_avg end as One,
case when status = 'two' then response_avg end as Two,
...,
from t1

This:

  band |   month    |  One  |  Two  
 ------+------------+-------+---------
    1  |  2018-01   |  3.41 |  NULL    
    2  |  2018-01   |  3.55 |  NULL
    1  |  2018-01   |  NULL |  2.55     
    2  |  2018-01   |  NULL |  4.61
    1  |  2018-02   |  1.55 |  NULL    
    2  |  2018-02   |  2.43 |  NULL
    1  |  2018-02   |  NULL |  4.33     
    2  |  2018-02   |  NULL |  3.44

Whereas I want

  band |   month    |  One  |  Two  
 ------+------------+-------+---------
    1  |  2018-01   |  3.41 |  2.55    
    2  |  2018-01   |  3.55 |  4.61
    1  |  2018-02   |  1.55 |  2.55     
    2  |  2018-02   |  2.43 |  4.61

I understand that the status column is causing this but can't figure out how to fix it.

I've tried a good few methods (multiple temp tables, sub-selects to remove the "status" due to default grouping) from different questions I found on here but keep ending up with the same result. Any help appreciated

With T1 as ... is not a temp table but a CTE https://www.sqlite.org/lang_with.html — Daniel E., Jan 16 '19 at 16:04
Does this help me fix my pivot? Honest question cause I don't really understand the linked reference — DublinMeUp, Jan 16 '19 at 16:44

score 0 · Accepted Answer · answered Jan 16 '19 at 16:51

0

The trick when you are using CASE/WHEN is to use aggregative functions like MAX and then group by all the non-aggragate columns :

SELECT 
   band,
   Month,
   MAX(CASE 
          when status = 'one' then response_avg
      END) as One,
   MAX(CASE 
          when status = 'two' then response_avg
      END) as Two
FROM t1
GROUP BY band,
   Month

answered Jan 16 '19 at 16:51

Daniel E.

2,440
1
14
24

Ah I see now! Should have figured that out myself. Thanks for the help – DublinMeUp Jan 17 '19 at 13:57

SQLite pivot producing alternate NULLS

1 Answers1