Home » SQL & PL/SQL » SQL & PL/SQL » Display Comma Separated Values (11g)
Display Comma Separated Values [message #668893] |
Wed, 21 March 2018 01:23 |
|
glmjoy
Messages: 187 Registered: September 2011 Location: KR
|
Senior Member |
|
|
Thanks in Advance
CREATE TABLE ABC (TOTAL_NAME VARCHAR2(4000));
insert into ABC VALUES('2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1');
insert into ABC VALUES('2238,GASTRIC VARICEAL INJECTION ( GENERAL,19500,19500,19500,19500,19500,1');
insert into ABC VALUES('2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1');
insert into ABC VALUES('2240,GRAFT STUDY,39000,39000,39000,39000,39000,1');
select TOTAL_NAME from abc
TOTAL_NAME
----------------------------------------------------------------------------------------------------
2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1
2240,GRAFT STUDY,39000,39000,39000,39000,39000,1
2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1
2238,GASTRIC VARICEAL INJECTION ( GENERAL,19500,19500,19500,19500,19500,1
I want to display this comma separated column I am not getting what I am doing wrong in display record with Alias
SELECT REGEXP_SUBSTR (total_name, '[^,]+', 1, 1)A,
REGEXP_SUBSTR (total_name, '[^,]+', 1, 2)B,
REGEXP_SUBSTR (total_name, '[^,]+', 1, 3)C,
REGEXP_SUBSTR (total_name, '[^,]+', 1, 4)D,
REGEXP_SUBSTR (total_name, '[^,]+', 1, 5)E,
REGEXP_SUBSTR (total_name, '[^,]+', 1, 6)F,
REGEXP_SUBSTR (total_name, '[^,]+', 1, 7)G,
REGEXP_SUBSTR (total_name || ',', '[^,]+', 1, 8)H
FROM abc;
I want Result
A B C D E F G H
2237 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2240 GRAFT STUDY 39000 39000 39000 39000 39000 1
2239 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2238 GASTRIC VARICEAL INJECTION ( GENERAL 19500 19500 19500 19500 19500 1
MY Result
------------
A
----------------------------------------------------------------------------------------------------
B
----------------------------------------------------------------------------------------------------
C
----------------------------------------------------------------------------------------------------
D
----------------------------------------------------------------------------------------------------
E
----------------------------------------------------------------------------------------------------
F
----------------------------------------------------------------------------------------------------
G
----------------------------------------------------------------------------------------------------
H
----------------------------------------------------------------------------------------------------
2237
GASTROSCOPY+BANDLIGATION EXC BAND
11232
11232
11232
11232
11232
A
----------------------------------------------------------------------------------------------------
B
----------------------------------------------------------------------------------------------------
C
----------------------------------------------------------------------------------------------------
D
----------------------------------------------------------------------------------------------------
E
----------------------------------------------------------------------------------------------------
F
----------------------------------------------------------------------------------------------------
G
----------------------------------------------------------------------------------------------------
H
----------------------------------------------------------------------------------------------------
1
2238
GASTRIC VARICEAL INJECTION ( GENERAL
19500
19500
19500
A
----------------------------------------------------------------------------------------------------
B
----------------------------------------------------------------------------------------------------
C
----------------------------------------------------------------------------------------------------
D
----------------------------------------------------------------------------------------------------
E
----------------------------------------------------------------------------------------------------
F
----------------------------------------------------------------------------------------------------
G
----------------------------------------------------------------------------------------------------
H
----------------------------------------------------------------------------------------------------
19500
19500
1
2239
GASTROSCOPY+BANDLIGATION EXC BAND
11232
A
----------------------------------------------------------------------------------------------------
B
----------------------------------------------------------------------------------------------------
C
----------------------------------------------------------------------------------------------------
D
----------------------------------------------------------------------------------------------------
E
----------------------------------------------------------------------------------------------------
F
----------------------------------------------------------------------------------------------------
G
----------------------------------------------------------------------------------------------------
H
----------------------------------------------------------------------------------------------------
11232
11232
11232
11232
1
2240
A
----------------------------------------------------------------------------------------------------
B
----------------------------------------------------------------------------------------------------
C
----------------------------------------------------------------------------------------------------
D
----------------------------------------------------------------------------------------------------
E
----------------------------------------------------------------------------------------------------
F
----------------------------------------------------------------------------------------------------
G
----------------------------------------------------------------------------------------------------
H
----------------------------------------------------------------------------------------------------
GRAFT STUDY
39000
39000
39000
39000
39000
1
A
----------------------------------------------------------------------------------------------------
B
----------------------------------------------------------------------------------------------------
C
----------------------------------------------------------------------------------------------------
D
----------------------------------------------------------------------------------------------------
E
----------------------------------------------------------------------------------------------------
F
----------------------------------------------------------------------------------------------------
G
----------------------------------------------------------------------------------------------------
H
----------------------------------------------------------------------------------------------------
|
|
|
|
|
|
|
|
|
|
Re: Display Comma Separated Values [message #668917 is a reply to message #668893] |
Thu, 22 March 2018 11:58 |
|
Barbara Boehmer
Messages: 9095 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You need to set your linesize and column formats, assuming that you are using SQL*Plus. Please see the reproduction of problem and correction below.
-- reproduction of problem:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE ABC (TOTAL_NAME VARCHAR2(4000));
Table created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2238,GASTRIC VARICEAL INJECTION ( GENERAL,19500,19500,19500,19500,19500,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2240,GRAFT STUDY,39000,39000,39000,39000,39000,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> Select TOTAL_NAME FROM ABC
2 /
TOTAL_NAME
--------------------------------------------------------------------------------
2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1
2238,GASTRIC VARICEAL INJECTION ( GENERAL,19500,19500,19500,19500,19500,1
2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1
2240,GRAFT STUDY,39000,39000,39000,39000,39000,1
4 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT REGEXP_SUBSTR (total_name, '[^,]+', 1, 1)A,
2 REGEXP_SUBSTR (total_name, '[^,]+', 1, 2)B,
3 REGEXP_SUBSTR (total_name, '[^,]+', 1, 3)C,
4 REGEXP_SUBSTR (total_name, '[^,]+', 1, 4)D,
5 REGEXP_SUBSTR (total_name, '[^,]+', 1, 5)E,
6 REGEXP_SUBSTR (total_name, '[^,]+', 1, 6)F,
7 REGEXP_SUBSTR (total_name, '[^,]+', 1, 7)G,
8 REGEXP_SUBSTR (total_name || ',', '[^,]+', 1, 8)H
9 FROM abc
10 /
A
--------------------------------------------------------------------------------
B
--------------------------------------------------------------------------------
C
--------------------------------------------------------------------------------
D
--------------------------------------------------------------------------------
E
--------------------------------------------------------------------------------
F
--------------------------------------------------------------------------------
G
--------------------------------------------------------------------------------
H
--------------------------------------------------------------------------------
2237
GASTROSCOPY+BANDLIGATION EXC BAND
11232
11232
11232
11232
11232
1
2238
GASTRIC VARICEAL INJECTION ( GENERAL
19500
19500
19500
19500
19500
1
2239
GASTROSCOPY+BANDLIGATION EXC BAND
11232
11232
11232
11232
11232
1
2240
GRAFT STUDY
39000
39000
39000
39000
39000
1
4 rows selected.
-- correction:
SCOTT@orcl_12.1.0.2.0> SET LINESIZE 130
SCOTT@orcl_12.1.0.2.0> COLUMN A FORMAT A10 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN B FORMAT A45 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN C FORMAT A10 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN D FORMAT A10 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN E FORMAT A10 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN F FORMAT A10 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN G FORMAT A10 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN H FORMAT A10 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> SELECT REGEXP_SUBSTR (total_name, '[^,]+', 1, 1)A,
2 REGEXP_SUBSTR (total_name, '[^,]+', 1, 2)B,
3 REGEXP_SUBSTR (total_name, '[^,]+', 1, 3)C,
4 REGEXP_SUBSTR (total_name, '[^,]+', 1, 4)D,
5 REGEXP_SUBSTR (total_name, '[^,]+', 1, 5)E,
6 REGEXP_SUBSTR (total_name, '[^,]+', 1, 6)F,
7 REGEXP_SUBSTR (total_name, '[^,]+', 1, 7)G,
8 REGEXP_SUBSTR (total_name || ',', '[^,]+', 1, 8)H
9 FROM abc
10 /
A B C D E F G H
---------- --------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
2237 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2238 GASTRIC VARICEAL INJECTION ( GENERAL 19500 19500 19500 19500 19500 1
2239 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2240 GRAFT STUDY 39000 39000 39000 39000 39000 1
4 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sun May 19 08:43:15 CDT 2024
|