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 |
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 |
|
Michel Cadot
Messages: 68693 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 #660530 is a reply to message #660520] |
Mon, 20 February 2017 06:23 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 08:13:46 CDT 2024
|