This content has been marked as final. Show 5 replies
user12144220 wrote:hows does actual report generation know, decide, or determine
I trying build a dynamic report with unlimited rows and columns. So I have 3 tables. The first table has report names mean while the second and the third tables have rows and columns information respectively.
-- table for report
-- table for report rows
rowid rowname reportid
1 soap 1
2 shampoo 1
--table for report columns
colid colname reportid
1 liquid 1
2 kids 1
From those tables, I want to have a result like:
rowname liquid 1 kids 2
How do I obtain this kind of result using pivot in oracle 11g? If I can have, end users have to enter some values to the soap and liquid row, soap and kids rows,... Then I have to execute some query getting new values and column id.
from which table the actual columns are to be obtained?
some,many, most reports join two or more tables.
How does this design support joining tables required for reporting against more than 1 table?
some,many,most reports contain computed values like SUM(), COUNT(), etc.
Are you conceding that none of your reports will contain any of these values?
WITH t AS ( SELECT 1 reportid,'purchase' reportname FROM dual UNION ALL SELECT 2 reportid,'vendor' reportname FROM dual ),t2 AS ( SELECT 1 rid,'soap' rname,1 reportid FROM dual UNION ALL SELECT 2 rid,'shampoo' rname,1 reportid FROM dual ),t3 AS ( SELECT 1 colid,'liquid' colname,1 reportid FROM dual UNION ALL SELECT 2 colid,'kids' colname,1 reportid FROM dual ),t4 as ( SELECT listagg(t3.colname,',') within group(order by null) cpln,listagg(t3.colid,',') within group(order by null) cplm FROM t,t2,t3 WHERE t.reportid=t2.rid AND t.reportid=t3.colid ) select regexp_substr(cpln,'[^,]+',1,2) c,regexp_substr(cplm,'[^,]+',1,1) d,regexp_substr(cpln,'[^,]+') a,regexp_substr(cplm,'[^,]+',1,2) b from t4 union SELECT rname,null,null,null FROM t2,t3 where t2.rid=t3.colid C D A B liquid 1 kids 2 shampoo - - - soap - - -
Thank all for quick help.
Since I'm trying to build dynamic report I don't know how many columns or rows do I have. So, in my opinion, I can't union results.
rowname liquid 1 kids 2
soap + + + +
shampoo + + + +
If have a grid like above end users can enter some values. So plus signs should be changed input values.
user12144220 wrote:Then you need dyanamic SQL.
... Since I'm trying to build dynamic report I don't know how many columns or rows do I have. ...
Relational databases were designed to handle dynamic numbers of rows, but the number of columns in a result set is hard-coded into the query.
Using the Oracle 11 PIVOT feature, you can put results into an XMLTYPE column, which, when rendered by HTML, will have a dynamic numbr of columns, even though the result set only has 1 column.
You can also use string aggregation to produce a big VARCHAR2 column that is formatted so it looks like a dynamic number of columns.
If you'd like help, post your best attempt, a little sample data (CREATE TABLE and INSERT statements), and the SQL results you want to get from that data.
To show the dynamic aspect of this problem, it would help if you psoted a couple of sets of data and the results from that data. For example: "If I have this data ... then the results shopuld be ... but if I add these rows to that data ... then the results should be ...".
Always give your exact version of Oracle. LISTAGG (available only in Oracle 11.2) might help in this problem.