如何在单行中合并多行，Oracle [英] How to combine multiple rows in a single row, oracle

问题描述

我有以下记录:

我想返回这样的结果:

加入许多表后，我得到了这个结果.所以仍然不知道要达到这个要求.

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

注意:我已经尝试了分组方式，但是没有用.

查询:

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";

谢谢

推荐答案

有一个函数名称LISTAGG，但在oracle 10g中不起作用.因此，我们可以使用 WM_CONCAT内置函数代替解决了这个问题.

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.

如果您没有运行11g Release 2或更高版本，但是正在运行存在WM_CONCAT函数的数据库版本，则这是一种零工作解决方案，因为它可以为您执行聚合.它实际上是下面描述的用户定义的聚合函数的示例，但是Oracle已经为您完成了所有工作.

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.

但是只有WM_CONCAT函数的问题是它不会删除重复的单词.因此，我们需要将其与DISTINCT关键字一起使用并解决问题.这是我的最终查询:

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;

这篇关于如何在单行中合并多行，Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！