You Asked
Is it possible to create table like structure with sql what i want is display output in matrix format like if i have 3 rows and 6 columns then it should provide desire output for same.
below are the scripts:
create table t1 (id number, name varchar2(100), col1 number, col2 number, col3 number, col4 number, col5 number, col6 number, col7 number, col8 number, col9 number, col10 number, col11 number, col12 number, col13 number, col14 number, col15 number, col16 number, col17 number, col18 number, col19 number, col20 number, p_row number, p_col number);
Insert into t1 values ( 1,'Data-1',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,5,10);
Insert into t1 values ( 2,'Data-2',11,21,31,41,51,61,71,81,91,101,111,121,131,141,151,161,171,181,191,201,3,6);
Insert into t1 values ( 3,'Data-3',21,22,23,24,25,26,27,28,29,210,211,212,213,222,215,216,217,218,219,220,3,9);
So based on ID and p_row and p_col values it should produce the output. (below is 4x4 matrix table p_row = 4 and p_col = 4)
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
I want output like above.
thank you
and Connor said...
You could build a dynamic ref cursor for this, eg
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 (id number, name varchar2(100), col1 number, col2 number, col3 number, col4 number, col5 number,
2 col6 number, col7 number, col8 number, col9 number, col10 number, col11 number, col12 number, col13 number,
3 col14 number, col15 number, col16 number, col17 number, col18 number, col19 number, col20 number, p_row number, p_col number);
Table created.
SQL>
SQL>
SQL> Insert into t1 values ( 1,'Data-1',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,5,10);
1 row created.
SQL>
SQL> create or replace
2 function dyn_ref_cur(p_id int) return sys_refcursor is
3 rc sys_refcursor;
4 x int; y int;
5 stmt varchar2(2000) := 'select ';
6 begin
7 select p_row, p_col into x, y from t1 where id = p_id;
8
9 for i in 1 .. 20 loop
10 if mod(i,y) = 0 then
11 stmt := stmt || ' col'||i||' from t1 where id = '||p_id||chr(10)||' union all select ';
12 else
13 stmt := stmt || ' col'||i||',';
14 end if;
15
16
17 end loop;
18 stmt := substr(stmt,1,length(stmt)-17);
19 dbms_output.put_line(stmt);
20
21 open rc for stmt;
22 return rc;
23 end;
24 /
Function created.
SQL> sho err
No errors.
SQL>
SQL> set serverout on
SQL> variable r refcursor
SQL> exec :r := dyn_ref_cur(1);
select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10 from t1 where id = 1
union all select col11, col12, col13, col14, col15, col16, col17, col18, col19, col20 from t1 where id = 1
PL/SQL procedure successfully completed.
SQL> print r;
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18 19 20
2 rows selected.
SQL>
If the matrix requested does not evenly divide into 20, then you would to pad out the last select with nulls. That has been left as a reader exercise :-)
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment