How to insert blank rows to create a uniform output in an Oracle SQL query?-CodePudding

Currently, my query pulls incomplete raw data in the following example layout. There could be between 1 and 3 A's, B's, etc so I need some way to check the number of rows that currently exist and how many need to be added to A/B/etc to get them to all out 3 rows.

Column 4	Column 8
A	1
B	2
C	1
C	2
D	3
E	1
E	2
E	3
F	2

I would like to add in a number of blank columns to create a uniform layout:

Column 4	Column 8
A	1 *
A	2
A	3
B	1
B	2 *
B	3
C	1 *
C	2 *
C	3
D	1
D	2
D	3 *
E	1 *
E	2 *
E	3 *
F	1
F	2 *
F	3

My current query:

select 
DATABASE1.TABLE1.COLUMN4,
DATABASE2.TABLE3.COLUMN8

from DATABASE1.TABLE1

inner join DATABASE2.TABLE2 on DATABASE1.TABLE1.MATCHINGCOLUMN1 = DATABASE2.TABLE2. MATCHINGCOLUMN1
inner join DATABASE2.TABLE3 on DATABASE2.TABLE2. MATCHINGCOLUMN2 = DATABASE2.TABLE3. MATCHINGCOLUMN2

where Column10 = 'PreferredOrderType'
and Column1 in (00000000)

I've tried replacing INNER JOIN with variations of OUTER JOIN with no success. I've seen some suggestions to use UNION or COALESCE but I have not been able to successfully implement them.

Any help would be greatly appreciated!

CodePudding user response：

Create a table that just has the values 1, 2, 3 and join with it.

Schema (MySQL v8.0)

CREATE TABLE t (
  `Col4` VARCHAR(1)
);

INSERT INTO t
  (`Col4`)
VALUES
  ('A'),
  ('B'),
  ('C'),
  ('C'),
  ('D'),
  ('E'),
  ('E'),
  ('E'),
  ('F');

CREATE TABLE nums (
  `n` INTEGER
);

INSERT INTO nums
  (`n`)
VALUES
  ('1'),
  ('2'),
  ('3');

Query

SELECT Col4, n
FROM t
JOIN nums;

Col4	n
A	1
A	2
A	3
B	1
B	2
B	3
C	1
C	2
C	3
C	1
C	2
C	3
D	1
D	2
D	3
E	1
E	2
E	3
E	1
E	2
E	3
E	1
E	2
E	3
F	1
F	2
F	3

View on DB Fiddle

CodePudding user response：

Hello other approach in Oracle

select distinct col1, level from yourtable connect by level <=3 order by 1;