I have a question about a problem I am dealing with for the past couple days. Unfortunately, posting here is my last resort as I am running out of options. I have sample of data that looks like this:

ord_no	ls_prod_area	ls_prod_family_name	ls_prod_generic_name	ls_brand_name	ls_atc_code	ls_atc_desc	ls_indication
1	Medicinal Product	Aceclofenac	Aceclofenac	Aceclofenac 1.5% w/w cream	M01AB16	aceclofenac	Analgesic & Anti-inflammatory
1	Medicinal Product	Aceclofenac	Aceclofenac	Aceclofenac 100 mg tablets	M01AB16	aceclofenac	NSAIDS
1	Medicinal Product	Aceclofenac	Aceclofenac	Aceclofenac 100 mg tablets	M01AB16	aceclofenac	Anti-inflammatory
1	Medicinal Product	Aceclofenac	Aceclofenac	Aceclofenac 100 mg tablets	M01	ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS	Antiinflammatory

I need it to look like this for all the data (sample of arranged data is just for ord_no 1):

ord_no	ls_prod_area	ls_prod_family_name	ls_prod_generic_name	ls_brand_name	ls_atc_code	ls_atc_desc	ls_indication
1	Medicinal Product	Aceclofenac	Aceclofenac	Aceclofenac 1.5% w/w cream	M01AB16	aceclofenac	Analgesic & Anti-inflammatory
1				Aceclofenac 100 mg tablets	M01	ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS	NSAIDS
1							Anti-inflammatory
1							Antiinflammatory

I am trying to get it done through Oracle SQL but I am having trouble with rows merging without having rows with null values.

This is SQL I am using:

select ord_no, PRODUCT_AREA, PRODUCT_FAMILY_NAME, PRODUCT_FAMILY_CODE, PRODUCT_GENERIC_NAME, BRAND_NAME, REFERENCE_PRODUCT,DESCRIPTION, ATC_CODE, ATC_DESCRIPTION, INDICATION, MEDDRA_LEVEL, MEDDRA_VERSION,MEDDRA_CODE from (with a as (select distinct LS_PROD_FAMILY_NAME,LS_PROD_GENERIC_NAME, ord_no, 1 as flag from tmp_product_family),
b as (select distinct LS_BRAND_NAME, ord_no, 2 as flag from tmp_product_family where LS_PROD_FAMILY_NAME is not null),
c as (select distinct LS_ATC_CODE, LS_ATC_DESC, ord_no, 3 as flag from tmp_product_family where LS_ATC_DESC is not null and LS_ATC_CODE is not null),
d as (select distinct LS_INDICATION, LS_IND_MEDDRA_LVL, LS_IND_MEDDRA_VER, LS_IND_MEDDRA_CODE, ord_no, 4 as flag from tmp_product_family where LS_INDICATION is not null)
select 'Medicinal product' as product_area, a.LS_PROD_FAMILY_NAME as Product_family_name, null as Product_family_code
, a.LS_PROD_GENERIC_NAME as Product_generic_name,
null as brand_name, null as reference_product, null as Description, null as atc_code, null as atc_description, null as indication , null as MedDRA_level,
null as meddra_version,null as meddra_code, a.ord_no, a.flag from a
UNION
select null as product_area, null as Product_family_name, null as Product_family_code
, null as Product_generic_name,
b.LS_BRAND_NAME as brand_name, null as reference_product, null as Description, null as atc_code, null as atc_description, null as indication , null as MedDRA_level,
null as meddra_version,null as meddra_code, b.ord_no, b.flag from b
UNION
select null as product_area, null as Product_family_name, null as Product_family_code
, null as Product_generic_name,
null as brand_name, null as reference_product, null as Description, c.LS_ATC_CODE as atc_code, c.LS_ATC_DESC as atc_description, null as indication , null as MedDRA_level,
null as meddra_version,null as meddra_code, c.ord_no, c.flag from c
UNION
select null as product_area, null as Product_family_name, null as Product_family_code
, null as Product_generic_name,
null as brand_name, null as reference_product, null as Description, null as atc_code, null as atc_description, d.LS_INDICATION as indication , d.LS_IND_MEDDRA_LVL as MedDRA_level,
d.LS_IND_MEDDRA_VER as meddra_version,d.LS_IND_MEDDRA_CODE as meddra_code, d.ord_no, d.flag from d) order by ord_no, flag;

And this is the result set I am getting:

ord_no	ls_prod_area	ls_prod_family_name	ls_prod_generic_name	ls_brand_name	ls_atc_code	ls_atc_desc	ls_indication
1	Medicinal product	Aceclofenac	Aceclofenac
1				Aceclofenac 100 mg tablets
1				Aceclofenac 1.5% w/w cream
1					M01	ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS
1					M01AB16	aceclofenac
1							Anti-inflammatory
1							Antiinflammatory
1							NSAIDS
1							Analgesic & Anti-inflammatory

I want the ls_brand_name, ls_atc_code, ls_atc_desc column values to be in first and second row, and ls_indication column values to be populated in rows 1,2,3 and 4 for ord_no 1. That would mean I have 4 rows for ord_no 1 which would then be formatted as it should be.

Does anyone have any ideas?

Thanks a lot for your help.

CodePudding user response：

As you asked about break, this is what I meant.

An ordinary query, where all "cells" are populated with data:

SQL> select d.dname, e.job, e.ename, e.sal
  2  from emp e join dept d on d.deptno = e.deptno
  3  order by d.dname, e.job;

DNAME          JOB       ENAME             SAL
-------------- --------- ---------- ----------
ACCOUNTING     CLERK     MILLER           1300
ACCOUNTING     MANAGER   CLARK            2450
ACCOUNTING     PRESIDENT KING             5000
RESEARCH       ANALYST   SCOTT            3000
RESEARCH       ANALYST   FORD             3000
RESEARCH       CLERK     ADAMS            1100
RESEARCH       CLERK     SMITH             840
RESEARCH       MANAGER   JONES            2975
SALES          CLERK     JAMES             950
SALES          MANAGER   BLAKE            2850
SALES          SALESMAN  MARTIN           1250
SALES          SALESMAN  WARD             1250
SALES          SALESMAN  ALLEN            1600
SALES          SALESMAN  TURNER           1500

14 rows selected.

If you put a break on e.g. department name and job, you get something that looks like result you need:

SQL> spool stefek.csv
SQL> break on dname on job
SQL> select d.dname, e.job, e.ename, e.sal
  2  from emp e join dept d on d.deptno = e.deptno
  3  order by d.dname, e.job;

DNAME          JOB       ENAME             SAL
-------------- --------- ---------- ----------
ACCOUNTING     CLERK     MILLER           1300
               MANAGER   CLARK            2450
               PRESIDENT KING             5000
RESEARCH       ANALYST   SCOTT            3000
                         FORD             3000
               CLERK     ADAMS            1100
                         SMITH             840
               MANAGER   JONES            2975
SALES          CLERK     JAMES             950
               MANAGER   BLAKE            2850
               SALESMAN  MARTIN           1250
                         WARD             1250
                         ALLEN            1600
                         TURNER           1500

14 rows selected.

SQL> spool off
SQL>

If you open that file in Excel, you get this:

in ver. 2016, go to "Data - From text" and follow the wizard
choose "Fixed width"
move separator lines where necessary
see the result

Connecting to SQL*Plus

You should know username, password and database you're connecting to. For example, I'm scanning my TNSNAMES.ORA file with the mctnsping utility (written by Michel Cadot; it doesn't require Oracle client to work. You can find it on OraFAQ Forum). Or, if you have TNSPING available, use it:

C:\Temp>mctnsping orcl

McTnsping Utility by Michel Cadot: Version 2021.12.03 on 20-PRO-2022 14:09:02

Copyright (c) Michel Cadot, 2016-2021. All rights reserved.

Using ping version 11

Used parameter files:
C:\Users\littlefoot\Documents\sqlnet.ora
C:\Users\littlefoot\Documents\tnsnames.ora

Found tnsnames.ora entry:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db_orcl)(PORT=1521))(CONNECT_DATA=(SID=orcl)))

Attempting to contact db_orcl:1521
OK (46 msec)

Now, use data you gathered; connect string is in format of @database_server:port/service_name:

C:\Temp>sqlplus scott/tiger@db_orcl:1521/orcl

SQL*Plus: Release 18.0.0.0.0 - Production on Uto Pro 20 14:09:17 2022
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
   
SQL> select * from dual;

D
-
X

SQL>

CodePudding user response：

If you have the minimal sample data:

CREATE TABLE table_name (a, b, c, d ) AS
  SELECT 'A1', 'B1', 'C1', 'D1' FROM DUAL UNION ALL
  SELECT 'A2', 'B1', 'C2', 'D1' FROM DUAL UNION ALL
  SELECT 'A3', 'B2', 'C2', 'D1' FROM DUAL UNION ALL
  SELECT 'A4', 'B3', 'C2', 'D1' FROM DUAL;

and you want to shuffle the data up the rows so that duplicates are not displayed then you can rank the values in each column, unpivot and then re-pivot the data:

SELECT a, b, c, d
FROM   (
  SELECT a, b, c, d,
         DENSE_RANK() OVER (ORDER BY a) AS a_rnk,
         DENSE_RANK() OVER (ORDER BY b) AS b_rnk,
         DENSE_RANK() OVER (ORDER BY c) AS c_rnk,
         DENSE_RANK() OVER (ORDER BY d) AS d_rnk
  FROM   table_name
)
UNPIVOT (
  (value, rnk) FOR key IN (
    (a, a_rnk) AS 'A',
    (b, b_rnk) AS 'B',
    (c, c_rnk) AS 'C',
    (d, d_rnk) AS 'D'
  )
)
PIVOT (
  MAX(value) FOR key IN ('A' AS a, 'B' AS b, 'C' AS c, 'D' AS d)
)
ORDER BY rnk

Which outputs:

A	B	C	D
A1	B1	C1	D1
A2	B2	C2	null
A3	B3	null	null
A4	null	null	null

fiddle