Home » SQL & PL/SQL » SQL & PL/SQL » Display Comma Separated Values (11g)
Display Comma Separated Values [message #668893] Wed, 21 March 2018 01:23 Go to next message
glmjoy
Messages: 166
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 #668894 is a reply to message #668893] Wed, 21 March 2018 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 19 March 2018 13:04

What about formatting your post as repeated to you many times?
And give feedback?

glmjoy wrote on Mon, 19 March 2018 16:09
Oh sorry for that

Liar! /forum/fa/4073/0/

[Updated on: Wed, 21 March 2018 02:13]

Report message to a moderator

Re: Display Comma Separated Values [message #668895 is a reply to message #668894] Wed, 21 March 2018 02:22 Go to previous messageGo to next message
glmjoy
Messages: 166
Registered: September 2011
Location: KR
Senior Member
Sorry again I didnt get your point of formatting oh i get now

[Updated on: Wed, 21 March 2018 02:28]

Report message to a moderator

Re: Display Comma Separated Values [message #668896 is a reply to message #668895] Wed, 21 March 2018 02:34 Go to previous messageGo to next message
glmjoy
Messages: 166
Registered: September 2011
Location: KR
Senior Member
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
Re: Display Comma Separated Values [message #668897 is a reply to message #668896] Wed, 21 March 2018 02:37 Go to previous messageGo to next message
glmjoy
Messages: 166
Registered: September 2011
Location: KR
Senior Member
Again I formatted here again the same result I am attaching file here
  • Attachment: abc.jpg
    (Size: 42.56KB, Downloaded 432 times)
Re: Display Comma Separated Values [message #668900 is a reply to message #668896] Wed, 21 March 2018 06:33 Go to previous messageGo to next message
EdStevens
Messages: 1233
Registered: September 2013
Senior Member
glmjoy wrote on Wed, 21 March 2018 02:34
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
From msg 668895:
Quote:

Sorry again I didnt get your point of formatting oh i get now
Apparently not.
Re: Display Comma Separated Values [message #668901 is a reply to message #668897] Wed, 21 March 2018 06:36 Go to previous messageGo to next message
EdStevens
Messages: 1233
Registered: September 2013
Senior Member
glmjoy wrote on Wed, 21 March 2018 02:37
Again I formatted here again the same result I am attaching file here
Many sites block attachments. Many of us refuse to open them for the same reason others block them. You don't need attachments to provide formatted code.
Here is an example of formatted code directly in the post:

C:\Users\estevens\Documents\sql>sqlplus estevens@orcl

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 21 06:35:21 2018

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

Enter password:
Last Successful login time: Mon Feb 26 2018 11:23:36 -05:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select sysdate from dual;

SYSDATE
---------
21-MAR-18

SQL>

Re: Display Comma Separated Values [message #668902 is a reply to message #668893] Wed, 21 March 2018 06:37 Go to previous messageGo to next message
EdStevens
Messages: 1233
Registered: September 2013
Senior Member
Isn't this the same as your other thread? http://www.orafaq.com/forum/t/204488/
Re: Display Comma Separated Values [message #668917 is a reply to message #668893] Thu, 22 March 2018 11:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.
Re: Display Comma Separated Values [message #668925 is a reply to message #668917] Fri, 23 March 2018 11:34 Go to previous message
glmjoy
Messages: 166
Registered: September 2011
Location: KR
Senior Member
Thanks a lot Barbara Boehmer
Previous Topic: Oracle 10 vs Oracle 12: order from SELECT statement
Next Topic: Merge 2 or more rows into 1
Goto Forum:
  


Current Time: Fri Aug 14 06:50:03 CDT 2020