Another type of dynamic SQL crosstab chart

Raw data:
CLASS1 CALLDATE CALLCOUNT
1 2005-08-08 40
1 2005-08-07 6
2 2005-08-08 77
3 2005-08-09 33
3
3 2005-08-07 21

After transpose:

CALLDATE CallCount1 CallCount2 CallCount3

------------------------------------------
2005-08-09 0 0 33

6 0 21

The test is as follows:

1. Create test tables and data
2. Create a ref cursor to prepare the output result set.
3. Create a dynamic SQL cross tabulation function to output the result set.
4. Run the test in SQL plus:

Create Table T (
Class1 varchar2 (2 byte ),
Calldate date,
Callcount integer
);


Insert into t
(Class1, calldate, callcount
)
Values ('1', to_date ('2014/1/123', 'Mm/DD/yyyy'), 40
);

Insert into t
(Class1, calldate, callcount
)
VALUES ('1', TO_DATE ('2014/1/123', 'Mm/DD/yyyy'), 6
);

Insert into t
(Class1, calldate, callcount
)
VALUES ('2', TO_DATE ('2014/1/123', 'Mm/DD/yyyy'), 77
);

Insert into t
(Class1, calldate, callcount
)
VALUES ('3', TO_DATE ('2014/1/123', 'Mm/DD/yyyy'), 33
);

Insert into t
(Class1, calldate, callcount
)
VALUES ('3', TO_DATE ('2014/1/123', 'Mm/DD/yyyy'), 9
);

Insert into t
(Class1, calldate, callcount
)
Values ('3', to_date ('2014/1/123', 'Mm/DD/yyyy'), 21
);

Commit;

 

Create or replace package pkg_getrecord
Is
Type myrctype is ref cursor;
End pkg_getrecord;
/

 

Create or replace function fn_rs
Return pkg_getrecord.myrctype
Is
S varchar2 (4000 );

Cursor C1
Is
Select ', sum (case when class1 ='
| Class1
| 'Then callcount else 0 end )'
| '"Callcount'
| Class1
| '"'C2
From t
Group by class1;

R1 C1 % rowtype;
List_cursor pkg_getrecord.myrctype;
BEGIN
S: = 'select calldate ';

OPEN c1;

LOOP
FETCH c1
INTO r1;

Exit when c1 % NOTFOUND;
S: = s | r1.c2;
End loop;

CLOSE c1;

S: = s | 'from T group by CallDate order by CallDate desc ';

OPEN list_cursor FOR s;

RETURN list_cursor;
END fn_rs;
/

 

Var results refcursor;
Exec: results: = fn_rs;
Print results;

CALLDATE CallCount1 CallCount2 CallCount3
---------------------------------------------
2005-08-09 0 0 33

6 0 21
 

Example 2:

Raw data:

C1 C2
1. Me
1 is
1 WHO
2 Zhi
2 channels
3 No

After transpose:

C1 col1 col2 col3
1. Who am I?
2 zhi dao
3 No

 

-- CREATE a test TABLE and data create table T1 (C1 NUMBER, C2 VARCHAR2 (20 BYTE); Insert into T1 Values (1, 'I'); Insert into T1 Values (1, 'Yes'); Insert into T1 Values (1, 'who'); Insert into T1 Values (2, 'zhi'); Insert into T1 Values (2, 'dao'); Insert into T1 Values (3, 'no'); COMMIT; -- write the transpose function create or replace function fn_rs RETURN SYS_REFCURSORIS s VARCHAR2 (32767 ): = 'select c1'; CURSOR c1 IS select', MAX (case when t1.r1 = '| r1 | 'then c2 else null end) col '| r1 c2 FROM (SELECT c1, c2, ROW_NUMBER () OVER (partition by c1 order by c1) r1 FROM t1) t1 group by r1; r1 c1 % ROWTYPE; list_cursor SYS_REFCURSOR; begin open c1; loop fetch c1 INTO r1; exit when c1 % NOTFOUND; s: = s | r1.c2; end loop; CLOSE c1; s: = s | 'from (SELECT c1, c2, ROW_NUMBER () OVER (partition by c1 order by c1) r1 FROM t1) t1 group by c1'; OPEN list_cursor FOR s; RETURN list_cursor; END fn_rs;/-- Test SELECT fn_rs from dual;