Oracle wm_concat to get one row then Case to check value

I just want to sort by Painter or not Painter.

Oracle 10g

Example Data:

job_type

  • cook
  • painter
  • programmer
  • mailman
  • receptionist
  • homemaker
  • father
  • mother
  • etc

A person can have one line for each job type but they can have several jobs.

SELECT   ...(LIST OF FIELDS)...,
         WM_CONCAT(job_type) AS myJob_Type
FROM     (MYTABLE)
GROUP BY ...(LIST OF FIELDS)...

This gives me all of the job_type's in myJob_Type in a comma delimited list. This works.

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.

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

Another option tried.

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 

Any suggestions.

Answers


You could try:

select   ...
         wm_concat(job_type) as myJob_Type,
         sum(decode(job_type,'Painter',1,0))
from     ...
group by ...

Need Your Help

Spinlock sync in VC++ without mutex.h

c++ multithreading winapi visual-c++ synchronization

I'm kind of lost here. I believe what I have here is absolutely thread safe, but I'm not sure it is. I need a very quick spinlock mechanism for small parts of a more complex lock that optimizes t...

Put business logic in one place with Redux

javascript redux

I like Redux. It is simple and powerful.