title: SQLite: group_concat() multiple columns
body: <p>I has a problem:</p>
  
  <p>In my SQLite (sqlite3 on android) database I have a table like so</p>
  
  <pre><code>company |  name    | job
  --------------------------
        1 |  'Peter' | 'Manager'
        1 |  'Jim'   | (null)
        2 |  'John'  | 'CEO'
        2 |  'Alex'  | 'Developer'
        3 |  'Lisa'  | (null)
  </code></pre>
  
  <p>and I'd like to get to</p>
  
  <pre><code>company | formated
  --------------------------------------
        1 | 'Peter (Manager), Jim'
        2 | 'John (CEO), Alex (Developer)'
        3 | 'Lisa'
  </code></pre>
  
  <p>What I got so far is</p>
  
  <pre><code>SELECT group_concat(concat) FROM (
      SELECT
          CASE 
              WHEN job IS NULL THEN name
              ELSE name || ' (' || job || ')'
          END AS concat
      FROM jobs
  )
  </code></pre>
  
  <p>which gives me all in one string</p>
  
  <pre><code>'Peter (Manager), Jim, John (CEO), Alex (Developer), Lisa'
  </code></pre>
  
  <p>Although that is quite good already, it's still not what I want. And at that point I fail to understand how I have to combine things to get what I want.</p>
  
  <p>On a sidenote: Is there any good tutorial on complexer queries? So far I've only found some snippets but nothing that really explains how such a thing can be built</p>