Home » SQL & PL/SQL » SQL & PL/SQL » Comma separate value with column value as column name (11.2.0.4)
Comma separate value with column value as column name [message #660508] Sun, 19 February 2017 23:37 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I have one requirement as below. I have got the output for comma separate by using below query but not like below output. Please anyone can help for query.
SELECT t.a1,
        trim(regexp_substr(t.a2, '[^,]+', 1, lines.column_value)) text
    FROM test t,
      TABLE (CAST (MULTISET
      (SELECT LEVEL FROM dual
              CONNECT BY instr(t.a2, ',', 1, LEVEL - 1) > 0
      ) AS sys.odciNumberList ) ) lines
    ORDER BY a1, lines.column_value;
CREATE TABLE TEST
   (	A1 VARCHAR2(10 BYTE), 
	A2 VARCHAR2(1000 BYTE)
   );

insert into test values('x','1,2,3,4');
insert into test values('y','a,b,c');
insert into test values('z','11,12,13,14');

o/p:

x     y      z
---  ---    ---
1     a      11
2     b      12
3     c      13
4
Re: Comma separate value with column value as column name [message #660509 is a reply to message #660508] Mon, 20 February 2017 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You are almost there, just add the "column_value" column to your query:
SQL> SELECT t.a1, lines.column_value,
  2          trim(regexp_substr(t.a2, '[^,]+', 1, lines.column_value)) text
  3      FROM test t,
  4        TABLE (CAST (MULTISET
  5        (SELECT LEVEL FROM dual
  6                CONNECT BY instr(t.a2, ',', 1, LEVEL - 1) > 0
  7        ) AS sys.odciNumberList ) ) lines
  8      ORDER BY a1, lines.column_value;
A1         COLUMN_VALUE TEXT
---------- ------------ --------------------
x                     1 1
x                     2 2
x                     3 3
x                     4 4
y                     1 a
y                     2 b
y                     3 c
z                     1 11
z                     2 12
z                     3 13
z                     4 14
Now you just have to group by this "column_value":
SQL> select max(decode(a1, 'x', text)) x,
  2         max(decode(a1, 'y', text)) y,
  3         max(decode(a1, 'z', text)) z
  4  from (
  5  SELECT t.a1, lines.column_value,
  6          trim(regexp_substr(t.a2, '[^,]+', 1, lines.column_value)) text
  7      FROM test t,
  8        TABLE (CAST (MULTISET
  9        (SELECT LEVEL FROM dual
 10                CONNECT BY instr(t.a2, ',', 1, LEVEL - 1) > 0
 11        ) AS sys.odciNumberList ) ) lines
 12  )
 13  group by column_value
 14  ORDER BY column_value;
X                    Y                    Z
-------------------- -------------------- --------------------
1                    a                    11
2                    b                    12
3                    c                    13
4                                         14

Re: Comma separate value with column value as column name [message #660520 is a reply to message #660509] Mon, 20 February 2017 04:36 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you Micheal.

You are right but if a1 column contains millions of records in the table. Please let me know how to handle this.
Re: Comma separate value with column value as column name [message #660530 is a reply to message #660520] Mon, 20 February 2017 06:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8893
Registered: November 2002
Location: California, USA
Senior Member
You can generate the query dynamically, looping through the distinct values of a1 as demonstrated below.

SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_sql  VARCHAR2(32767);
  3  BEGIN
  4    v_sql := 'SELECT ';
  5    FOR i IN
  6  	 (SELECT DISTINCT a1
  7  	  FROM	 test
  8  	  ORDER  BY a1)
  9    LOOP
 10  	 v_sql := v_sql || 'MAX(DECODE(a1,''' || i.a1 || ''',text)) ' || i.a1 || ',';
 11    END LOOP;
 12    v_sql := RTRIM (v_sql, ',') ||
 13  	 ' FROM  (SELECT t.a1, lines.COLUMN_VALUE,
 14  			 TRIM (REGEXP_SUBSTR (t.a2, ''[^,]+'', 1, lines.COLUMN_VALUE)) text
 15  		  FROM	 test t,
 16  			 TABLE
 17  			   (CAST
 18  			     (MULTISET
 19  			       (SELECT	LEVEL
 20  				FROM	DUAL
 21  				CONNECT BY INSTR (t.a2, '','', 1, LEVEL - 1) > 0)
 22  			    AS SYS.ODCINUMBERLIST)) lines)
 23  	   GROUP  BY COLUMN_VALUE
 24  	   ORDER  BY COLUMN_VALUE';
 25    OPEN :g_ref FOR v_sql;
 26  END;
 27  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_ref

X          Y          Z
---------- ---------- ----------
1          a          11
2          b          12
3          c          13
4                     14

4 rows selected.
Re: Comma separate value with column value as column name [message #660537 is a reply to message #660520] Mon, 20 February 2017 07:49 Go to previous message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
grpatwari wrote on Mon, 20 February 2017 11:36
Thank you Micheal.

You are right but if a1 column contains millions of records in the table. Please let me know how to handle this.
Do you think you can read a result with millions of columns?

Previous Topic: Synonym switching
Next Topic: unique result
Goto Forum:
  


Current Time: Sun Oct 21 02:07:59 CDT 2018