CODEWITHSUNDEEP
oracle-databaseoracle10g
Nathan Stanford
Nathan Stanford

Reputation: 1394

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

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.

Upvotes: 0

Views: 1729

Answers (1)

Xophmeister
Xophmeister

Reputation: 9211

You could try:

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

Upvotes: 1

Related Questions