How to combine multiple rows in a single row, oracle

Question

I have following record:

And I want to return result like this:

I have got this result after joining many tables. So still no idea to achieve this requirement.

Note: I have tried with group by but didn't work.

Query:

SELECT 
P.CODE AS "projectNumber", 
P.NAME AS "projectName", 
P.START_DATE AS "startDate", 
P.END_DATE AS "endDate",
TRIM (VP.firstName || ' ' || VP.lastName) AS "vp",
TRIM (SRPM.firstName || ' ' || SRPM.lastName) AS "srpm",
TRIM (PM.firstName || ' ' || PM.lastName) AS "pm",
TRIM (SUP.firstName || ' ' || SUP.lastName) AS "sup",
TRIM (PE.firstName || ' ' || PE.lastName) AS "pe"
FROM DA.ROJECT_TABLE P
LEFT JOIN BA.teams_v VP on (P.CODE=VP.projectnumber and VP.code not in ('30', '85', 'ZZ') and VP.employoeenumber is not null and VP.status='A' and VP.projectrolename in ('Sr. Vice President, CFO','Senior Vice President','Vice President','President','Sr. Vice President','Vice President of Operations','Vice President', 'Chief Estimator','Vice President, Senior Project Manager','Vice President of Preconstruction and Estimating','Executive Vice President','Vice President, Sr. Project Manager'))
LEFT JOIN BA.teams_v SRPM on (P.CODE=SRPM.projectnumber and SRPM.code not in ('30', '85', 'ZZ') and SRPM.employoeenumber is not null and SRPM.status='A' and SRPM.projectrolename in ('Vice President/Sr. Project Manager','Senior Project Manager','Vice President, Senior Project Manager','Vice President, Sr. Project Manager'))
LEFT JOIN BA.teams_v PM on (P.CODE=PM.projectnumber and PM.code not in ('30', '85', 'ZZ') and PM.employoeenumber is not null and PM.status='A' and PM.projectrolename in ('Project Manager','Assistant Project Manager','Manager, Project Accounting','Asst. Project Manager'))
LEFT JOIN BA.teams_v SUP on (P.CODE=SUP.projectnumber and SUP.code not in ('30', '85', 'ZZ') and SUP.employoeenumber is not null and SUP.status='A' and SUP.projectrolename in ('Assistant Superintendent','CMatt - Superintendent','General Superintendent'))
LEFT JOIN BA.teams_v PE on (P.CODE=PE.projectnumber and PE.code not in  ('30', '85', 'ZZ') and PE.employoeenumber is not null and PE.status='A' and PE.projectrolename in ('Senior Project Engineer','Sr. Project Engineer','Intern Asst. Project Engineer','Assistant Project Engineer','Intern Project Engineer','Project Engineer'))
WHERE P.PMP_COMP_CODE NOT IN ('30', '85', 'ZZ')AND P.STATUS_CODE NOT IN ('CLOSED') AND P.PCODE='ALL' AND NVL(LENGTH(TRIM(TRANSLATE(substr(P.CODE, 1, 1), ' +-.012*34-56+789LP', ' '))),'0') = 0 ORDER BY "projectNumber";

Thank you

Possible duplicate of [Concatenate and group multiple rows in Oracle](http://stackoverflow.com/questions/12558509/concatenate-and-group-multiple-rows-in-oracle) — Kai Adelmann, Apr 06 '17 at 13:37
you can have a look at LISTAGG function along with group by, will help you in reaching the solution — Sudipta Mondal, Apr 06 '17 at 13:38
Look into your previous select instead of a quick and dirty workaround after the fact — Mihai, Apr 06 '17 at 13:38
Edit your question and provide the query that you have tried. — Gordon Linoff, Apr 06 '17 at 13:46
@Mihai These are same projects assigned to different persons (VP, sup, PE, etc.). So this is the best result I can get. I have displayed by manipulating in Java. But the problem is a performance issue because of huge records. So just trying to select with a query. — Yubaraj, Apr 06 '17 at 13:53
You actually want to pivot. Prefer GROUP BY over (many) outer joins. Read this article: http://modern-sql.com/use-case/pivot Especially the part about "The Special Case of EAV". — Markus Winand, Apr 06 '17 at 18:09
@Yubaraj you are correct, LISTAGG came in 11g. You could try the following where you create your own aggregate function. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9529467800346786851 — unleashed, Apr 07 '17 at 14:28

score 0 · Answer 1 · answered Apr 06 '17 at 16:00

Try this ...



'SELECT distinct
P.CODE AS "projectNumber", 
P.NAME AS "projectName", 
P.START_DATE AS "startDate", 
P.END_DATE AS "endDate",

LISTAGG((VP.firstName || ' ' || VP.lastName), ' ') WITHIN GROUP (order by rownum) AS VP,
TRIM (SRPM.firstName || ' ' || SRPM.lastName) AS "srpm",
TRIM (PM.firstName || ' ' || PM.lastName) AS "pm",
TRIM (SUP.firstName || ' ' || SUP.lastName) AS "sup",
TRIM (PE.firstName || ' ' || PE.lastName) AS "pe"
FROM DA.ROJECT_TABLE P
LEFT JOIN BA.teams_v VP on (P.CODE=VP.projectnumber and VP.code not in ('30', '85', 'ZZ') and VP.employoeenumber is not null and VP.status='A' and VP.projectrolename in ('Sr. Vice President, CFO','Senior Vice President','Vice President','President','Sr. Vice President','Vice President of Operations','Vice President', 'Chief Estimator','Vice President, Senior Project Manager','Vice President of Preconstruction and Estimating','Executive Vice President','Vice President, Sr. Project Manager'))
LEFT JOIN BA.teams_v SRPM on (P.CODE=SRPM.projectnumber and SRPM.code not in ('30', '85', 'ZZ') and SRPM.employoeenumber is not null and SRPM.status='A' and SRPM.projectrolename in ('Vice President/Sr. Project Manager','Senior Project Manager','Vice President, Senior Project Manager','Vice President, Sr. Project Manager'))
LEFT JOIN BA.teams_v PM on (P.CODE=PM.projectnumber and PM.code not in ('30', '85', 'ZZ') and PM.employoeenumber is not null and PM.status='A' and PM.projectrolename in ('Project Manager','Assistant Project Manager','Manager, Project Accounting','Asst. Project Manager'))
LEFT JOIN BA.teams_v SUP on (P.CODE=SUP.projectnumber and SUP.code not in ('30', '85', 'ZZ') and SUP.employoeenumber is not null and SUP.status='A' and SUP.projectrolename in ('Assistant Superintendent','CMatt - Superintendent','General Superintendent'))
LEFT JOIN BA.teams_v PE on (P.CODE=PE.projectnumber and PE.code not in  ('30', '85', 'ZZ') and PE.employoeenumber is not null and PE.status='A' and PE.projectrolename in ('Senior Project Engineer','Sr. Project Engineer','Intern Asst. Project Engineer','Assistant Project Engineer','Intern Project Engineer','Project Engineer'))'

Yubaraj · Accepted Answer · 2017-05-19T03:42:56.633

There is a function name LISTAGG, but it doesn't work in oracle 10g. So we can use WM_CONCAT Built-in Function instead and it solves the problem.

If you are not running 11g Release 2 or above, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.

But the problem with only WM_CONCAT function is it does not remove duplicate words. So we need to use it with DISTINCT keyword and problem solves. Here is my final query:

SELECT 
P.CODE AS "projectNumber", 
P.NAME AS "projectName", 
P.START_DATE AS "startDate", 
P.END_DATE AS "endDate",
WM_CONCAT(DISTINCT TRIM (VP.firstName || ' ' || VP.lastName)) AS "vp",
WM_CONCAT(DISTINCT TRIM (SRPM.firstName || ' ' || SRPM.lastName)) AS "srpm",
WM_CONCAT(DISTINCT TRIM (PM.firstName || ' ' || PM.lastName)) AS "pm",
WM_CONCAT(DISTINCT TRIM (SUP.firstName || ' ' || SUP.lastName)) AS "sup",
WM_CONCAT(DISTINCT TRIM (PE.firstName || ' ' || PE.lastName)) AS "pe"
FROM DA.ROJECT_TABLE P
LEFT JOIN BA.teams_v VP on (P.CODE=VP.projectnumber and VP.code not in ('30', '85', 'ZZ') and VP.employoeenumber is not null and VP.status='A' and VP.projectrolename in ('Sr. Vice President, CFO','Senior Vice President','Vice President','President','Sr. Vice President','Vice President of Operations','Vice President', 'Chief Estimator','Vice President, Senior Project Manager','Vice President of Preconstruction and Estimating','Executive Vice President','Vice President, Sr. Project Manager'))
LEFT JOIN BA.teams_v SRPM on (P.CODE=SRPM.projectnumber and SRPM.code not in ('30', '85', 'ZZ') and SRPM.employoeenumber is not null and SRPM.status='A' and SRPM.projectrolename in ('Vice President/Sr. Project Manager','Senior Project Manager','Vice President, Senior Project Manager','Vice President, Sr. Project Manager'))
LEFT JOIN BA.teams_v PM on (P.CODE=PM.projectnumber and PM.code not in ('30', '85', 'ZZ') and PM.employoeenumber is not null and PM.status='A' and PM.projectrolename in ('Project Manager','Assistant Project Manager','Manager, Project Accounting','Asst. Project Manager'))
LEFT JOIN BA.teams_v SUP on (P.CODE=SUP.projectnumber and SUP.code not in ('30', '85', 'ZZ') and SUP.employoeenumber is not null and SUP.status='A' and SUP.projectrolename in ('Assistant Superintendent','CMatt - Superintendent','General Superintendent'))
LEFT JOIN BA.teams_v PE on (P.CODE=PE.projectnumber and PE.code not in  ('30', '85', 'ZZ') and PE.employoeenumber is not null and PE.status='A' and PE.projectrolename in ('Senior Project Engineer','Sr. Project Engineer','Intern Asst. Project Engineer','Assistant Project Engineer','Intern Project Engineer','Project Engineer'))
WHERE P.PMP_COMP_CODE NOT IN ('30', '85', 'ZZ')AND P.STATUS_CODE NOT IN ('CLOSED') AND P.PCODE='ALL' AND NVL(LENGTH(TRIM(TRANSLATE(substr(P.CODE, 1, 1), ' +-.012*34-56+789LP', ' '))),'0') = 0
GROUP BY P.CODE, P.NAME, P.START_DATE, P.END_DATE;

How to combine multiple rows in a single row, oracle

2 Answers2

Linked