Home » SQL & PL/SQL » SQL & PL/SQL » query to write rows to column values (11.2.0.4)
| query to write rows to column values [message #652014] |
Tue, 31 May 2016 04:53  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I have one requirement as below.Input and output is attached in the excel sheet.
CREATE TABLE "TEST_DATA"
( "PROJID" NUMBER(8,0) NOT NULL ENABLE,
"OBJID" NUMBER NOT NULL ENABLE,
"PROPTYPE" NUMBER(3,0) NOT NULL ENABLE,
"PROPID" NUMBER(6,0) NOT NULL ENABLE,
"TEXT_VALUE" VARCHAR2(1000 CHAR) ,
"NOTE_VALUE" VARCHAR2(4000 CHAR) ,
"NUMBER_VALUE" NUMBER ,
"DATE_VALUE" DATE,
);
REM INSERTING into TEST_DATA
SET DEFINE OFF;
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,1,128129,'103033',null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,1,105400,'EMOLWHI',null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,1,127761,'0',null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,1,127762,'0',null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,1,128130,'103033 04/03/2014',null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,2,128273,null,null,0,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,2,128274,null,null,0,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,3,109004,null,null,null,to_date('05-AUG-14','DD-MON-RR'));
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,3,109002,null,null,null,to_date('04-MAR-14','DD-MON-RR'));
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,3,108996,null,null,null,to_date('15-JUL-14','DD-MON-RR'));
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,3,115791,null,null,null,to_date('05-AUG-14','DD-MON-RR'));
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,3,105399,null,null,null,to_date('04-MAR-14','DD-MON-RR'));
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,3,109006,null,null,null,to_date('05-AUG-14','DD-MON-RR'));
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,5,115789,null,null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,5,108997,null,null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,5,115787,null,null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,5,115792,null,null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,7,105401,null,null,null,null);
Insert into TEST_DATA (PROJID,OBJID,PROPTYPE,PROPID,TEXT_VALUE,NOTE_VALUE,NUMBER_VALUE,DATE_VALUE) values (1562,144150,8,105398,null,null,null,null);
1. If proptype = 1 then propid values as rows and text_value column values as column value
2. If proptype = 2 then propid values as rows next to propid value 1 and number_value column values as column value
3. If proptype = 3 then propid values as rows next to propid value 2 and date_value column values as column value
Please help me for the query.
|
|
|
|
|
|
| Re: query to write rows to column values [message #652020 is a reply to message #652017] |
Tue, 31 May 2016 05:33  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Output is in excel sheet. Any way please find the output as below.
1. selecting the columns PROJID, OBJID, PROPTYPE (1, 2, 3 etc)
2. propid value is based on proptype 1, 2 and 3.
3. For example, propid values are 128129, 105400, 127761, 127762 for proptype is 1. So it has display the column values 128129, 105400, 127761, 127762 as header and text_Value is displayed under proid column. Same as proptype is 2 and 3 etc.
|
|
|
|
|
|
| Re: query to write rows to column values [message #652023 is a reply to message #652022] |
Tue, 31 May 2016 06:14 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Output
PROJID OBJID PROPTYPE 128129 105400 127761 127762 128130 128273 128274 109004 109002 108996
1562 144150 1 103033 EMOLWHI 0 0 103033 4/03/2014
1562 144150 2 0 0
1562 144150 3 5-Aug-14 4-Mar-14 15-Jul-14
|
|
|
|
|
|
| Re: query to write rows to column values [message #652065 is a reply to message #652023] |
Tue, 31 May 2016 14:22 |
 |
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_length NUMBER;
3 v_sql VARCHAR2(32767);
4 BEGIN
5 SELECT MAX (LENGTH (text_value)) INTO v_length FROM test_data;
6 v_sql := 'SELECT projid,objid,proptype';
7 FOR r IN (SELECT propid FROM test_data WHERE proptype IN (1,2,3) ORDER BY ROWID) LOOP
8 v_sql := v_sql || ',CAST(MAX(DECODE(propid,' || r.propid
9 || ',DECODE(proptype,1,text_value,2,number_value,3,date_value))) AS VARCHAR2('
10 || v_length || '))"' || r.propid || '"';
11 END LOOP;
12 v_sql := v_sql || ' FROM test_data WHERE proptype IN (1,2,3) GROUP BY projid,objid,proptype ORDER BY 1,2,3';
13 OPEN :g_ref FOR v_sql;
14 END;
15 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> PRINT g_ref
PROJID OBJID PROPTYPE 128129 105400 127761 127762 128130 128273 128274 109004 109002 108996 115791 105399 109006
---------- ---------- ---------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- -----------------
1562 144150 1 103033 EMOLWHI 0 0 103033 04/03/2014
1562 144150 2 0 0
1562 144150 3 Tue 05-Aug-2014 Tue 04-Mar-2014 Tue 15-Jul-2014 Tue 05-Aug-2014 Tue 04-Mar-2014 Tue 05-Aug-2014
3 rows selected.
|
|
|
|
|
|
| Re: query to write rows to column values [message #655329 is a reply to message #652314] |
Sun, 28 August 2016 23:55 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi,
Extended to this requirement. I would like to pass the table through function as a parameter. I am using the same below script but written in the function.
Please advice me on this.
create or replace function test_f return SYS_REFCURSOR is
g_ref SYS_REFCURSOR;
v_length long;
v_sql VARCHAR2(32767);
BEGIN
SELECT MAX (LENGTH (text_value)) INTO v_length FROM V_DATA6;
--v_sql := 'SELECT projid,objtypeid,objname';
v_sql := 'SELECT projid,BUSINESSOBJECT_ID,INSTANCE_ID';
FOR r IN (SELECT distinct ATTRIBUTE_ID FROM V_DATA6 WHERE projid=138 and BUSINESSOBJECT_ID=18 AND TEXT_VALUE IS NOT NULL --ORDER BY ROWID
) LOOP
v_sql := v_sql || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || r.ATTRIBUTE_ID
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE))) AS VARCHAR2('
|| v_length || '))"' || r.ATTRIBUTE_ID || '"';
END LOOP;
v_sql := v_sql || ' FROM V_DATA6 WHERE projid=138 GROUP BY projid,BUSINESSOBJECT_ID,INSTANCE_ID';-- ORDER BY 1,2,3';
OPEN g_ref FOR v_sql;
return g_ref;
END test_f;
|
|
|
|
| Re: query to write rows to column values [message #655331 is a reply to message #655329] |
Mon, 29 August 2016 00:55 |
|
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
grpatwari wrote on Sun, 28 August 2016 21:55Hi,
... I would like to pass the table through function as a parameter. ...
It is not clear from that description what you want to do. The code that you posted suggests that you want to create a function to return a ref cursor containing the same results as the ref cursor used with the anonymous PL/SQL block previously. I am guessing that you want to pass that returned ref cursor value from the function as an input parameter to some other function or procedure. Your new code uses a different table name and column names than what you provided previously. So, in the following demonstration, I have changed the table name, but used the previous data and column names. Note that the v_length variable needs to be a number data type, not long data type.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_f
2 RETURN SYS_REFCURSOR
3 AS
4 v_ref SYS_REFCURSOR;
5 v_length NUMBER;
6 v_sql VARCHAR2(32767);
7 BEGIN
8 SELECT MAX (LENGTH (text_value)) INTO v_length FROM V_DATA6;
9 v_sql := 'SELECT projid,objid,proptype';
10 FOR r IN (SELECT propid FROM V_DATA6 WHERE proptype IN (1,2,3) ORDER BY ROWID) LOOP
11 v_sql := v_sql || ',CAST(MAX(DECODE(propid,' || r.propid
12 || ',DECODE(proptype,1,text_value,2,number_value,3,date_value))) AS VARCHAR2('
13 || v_length || '))"' || r.propid || '"';
14 END LOOP;
15 v_sql := v_sql || ' FROM V_DATA6 WHERE proptype IN (1,2,3) GROUP BY projid,objid,proptype ORDER BY 1,2,3';
16 OPEN v_ref FOR v_sql;
17 RETURN v_ref;
18 END test_f;
19 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> SELECT test_f FROM DUAL
2 /
TEST_F
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
PROJID OBJID PROPTYPE 128129 105400 127761 127762 128130 128273 128274 109004 109002 108996 115791 105399 109006
---------- ---------- ---------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- -----------------
1562 144150 1 103033 EMOLWHI 0 0 103033 04/03/2014
1562 144150 2 0 0
1562 144150 3 Tue 05-Aug-2014 Tue 04-Mar-2014 Tue 15-Jul-2014 Tue 05-Aug-2014 Tue 04-Mar-2014 Tue 05-Aug-2014
3 rows selected.
|
|
|
|
| Re: query to write rows to column values [message #655333 is a reply to message #655331] |
Mon, 29 August 2016 01:26 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Sorry Barbara.
We are using the table name "V_DATA6" in the function. Instead I would like to pass the table name(wherever it is there in the function) dynamically and get the same result. I hope this clarifies.
Please advice on this.
|
|
|
|
|
|
| Re: query to write rows to column values [message #655369 is a reply to message #655333] |
Mon, 29 August 2016 15:26 |
|
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I understand now. Every place that the table name was used will have to have the p_table_name input parameter concatenated instead and dynamic SQL used, either with execute immediate or a ref cursor. Instead of using a for loop, I have opened the ref cursor and fetched from it below, then closed the ref cursor before opening it again for the v_sql.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_f
2 (p_table_name IN VARCHAR2)
3 RETURN SYS_REFCURSOR
4 AS
5 v_ref SYS_REFCURSOR;
6 v_length NUMBER;
7 v_sql VARCHAR2(32767);
8 v_propid NUMBER;
9 BEGIN
10 EXECUTE IMMEDIATE
11 'SELECT MAX (LENGTH (text_value)) FROM ' || p_table_name
12 INTO v_length;
13 v_sql := 'SELECT projid,objid,proptype';
14 OPEN v_ref FOR 'SELECT propid FROM ' || p_table_name || ' WHERE proptype IN (1,2,3) ORDER BY ROWID';
15 LOOP
16 FETCH v_ref INTO v_propid;
17 EXIT WHEN v_ref%NOTFOUND;
18 v_sql := v_sql || ',CAST(MAX(DECODE(propid,' || v_propid
19 || ',DECODE(proptype,1,text_value,2,number_value,3,date_value))) AS VARCHAR2('
20 || v_length || '))"' || v_propid || '"';
21 END LOOP;
22 CLOSE v_ref;
23 v_sql := v_sql || ' FROM ' || p_table_name ||
24 ' WHERE proptype IN (1,2,3) GROUP BY projid,objid,proptype ORDER BY 1,2,3';
25 OPEN v_ref FOR v_sql;
26 RETURN v_ref;
27 END test_f;
28 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> SELECT test_f ('V_DATA6') FROM DUAL
2 /
TEST_F('V_DATA6')
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
PROJID OBJID PROPTYPE 128129 105400 127761 127762 128130 128273 128274 109004 109002 108996 115791 105399 109006
---------- ---------- ---------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- -----------------
1562 144150 1 103033 EMOLWHI 0 0 103033 04/03/2014
1562 144150 2 0 0
1562 144150 3 Tue 05-Aug-2014 Tue 04-Mar-2014 Tue 15-Jul-2014 Tue 05-Aug-2014 Tue 04-Mar-2014 Tue 05-Aug-2014
3 rows selected.
1 row selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 18 15:57:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
