title: Oracle wm_concat to get one row then Case to check value
body: <p>I just want to sort by Painter or not Painter. </p>
  
  <p>Oracle 10g</p>
  
  <h1>Example Data:</h1>
  
  <p><strong>job_type</strong></p>
  
  <ul>
  <li>cook </li>
  <li>painter </li>
  <li>programmer </li>
  <li>mailman </li>
  <li>receptionist </li>
  <li>homemaker </li>
  <li>father </li>
  <li>mother</li>
  <li>etc</li>
  </ul>
  
  <p>A person can have one line for each job type but they can have several jobs.</p>
  
  <pre><code>SELECT   ...(LIST OF FIELDS)...,
           WM_CONCAT(job_type) AS myJob_Type
  FROM     (MYTABLE)
  GROUP BY ...(LIST OF FIELDS)...
  </code></pre>
  
  <p>This gives me all of the job_type's in myJob_Type in a comma delimited list.
  This works.</p>
  
  <p>Now I want a way that I can sort by myJob_Type when the job has 'Painter' in it. I don't care about any other job type. So my thought was to make a case where if Painter is in the myJob_Type but when I do get it to work I get muiltple lines again.</p>
  
  <pre><code>SELECT ...(LIST OF FIELDS)...,
  WM_CONCAT(job_type) AS myJob_Type,
  CASE WHEN(job_ype like '%Painter%') THEN '1'
  ELSE '0'
  END
  
  FROM     (MYTABLE)
  GROUP BY ...(LIST OF FIELDS)...,
           job_ype
  </code></pre>
  
  <p>Another option tried.</p>
  
  <pre><code>SELECT ...(LIST OF FIELDS)...,
  WM_CONCAT(job_type) AS myJob_Type,
  
  CASE WHEN(myJobType like '%Painter%') THEN '1'
  ELSE '0'
  END
  
  FROM      (MYTABLE)
  GROUP BY  ...(LIST OF FIELDS)...,
            myJobType 
  </code></pre>
  
  <p>Any suggestions.</p>