Transposing Rows as Columns for a Report

uparna · Mar 25, 2004

Hello ,
I have a table say "Coffee_Mapping" , and it has the foll 2 columns apart from others:
C_Bean_Id,
Coffee_Type

I have 4 Types of Coffee : Cappuchino , Espresso , Mocha and Boring.

There are some beans that can be used for all types of coffee, and some beans that can be used only for Mocha and some only for boring and Espresso , etc

Now i want a report like this (i really dont know how to portray a simple table in this posting : would you please copy paste this into a small file, call it whatever.html and then open it to see the desired o/p? ):
<html>

<body>

<div>

<table border=1>
<tr>
<td>
C_Bean_Id
</td>
<td>
Cappuchino
</td>
<td>
Espresso
</td>
<td>
Mocha
</td>
<td>
Boring
</td>
</tr>
<tr>
<td>
1
</td>
<td>
X
</td>
<td>
X
</td>
<td>
X
</td>
<td>
X
</td>
</tr>
<tr>
<td>
2
</td>
<td>
<![if !supportEmptyParas]> <![endif]><o

></o

>
</td>
<td>
<![if !supportEmptyParas]> <![endif]><o

></o

>
</td>
<td>
X
</td>
<td>
<![if !supportEmptyParas]> <![endif]><o

></o

>
</td>
</tr>
<tr>
<td>
3
</td>
<td>
<![if !supportEmptyParas]> <![endif]><o

></o

>
</td>
<td>
X
</td>
<td>
<![if !supportEmptyParas]> <![endif]><o

></o

>
</td>
<td>
X
</td>
</tr>
</table>

</div>

</body>

</html>

Note here that in the original case , the types of coffee are nearly 100 :- but they are a standard set. Apart from using a decode, is there any easy way to get this result using a single query? I am wary of using a 100 decodes to get this data in to a 100 columns. I am sure that this is a common requirement in the business world and Oracle must have a trick up its sleeve for this. I am yet ignorant of it.

Thanks !

Regards,
S. Jayaram Uparna .

SantaMufasa · Mar 25, 2004

Uparna,

There are dozens of methods that can solve your need.

Following is a "perky" little solution (pardon the pun) that does not use any DECODEs or column-conditional processing. It can handle hundreds of coffee types, becoming columns in your output.

Section 1 - COFFEE_TYPE table definition and population:

Code:

create table Coffee_type
	(ID		number
	,Name		varchar2(30));
insert into coffee_type values (1,'Cappuchino');
insert into coffee_type values (2,'Espresso');
insert into coffee_type values (3,'Mocha');
insert into coffee_type values (4,'Boring');

Section 2 - COFFEE_MAPPINGS table definition and population:

Code:

create table Coffee_mapping
	(C_Bean_Id	number
	,Coffee_type	number);
insert into coffee_mapping values (1,1);
insert into coffee_mapping values (1,2);
insert into coffee_mapping values (1,3);
insert into coffee_mapping values (1,4);
insert into coffee_mapping values (2,3);
insert into coffee_mapping values (3,2);
insert into coffee_mapping values (3,4);

Section 3 - PL/SQL code that creates a SQL script ("temp.sql") to populate and access the temporary Bean-usage table:

Code:

set serveroutput on
set echo off
set feedback off
spool temp.sql
declare
	cnt	number;
	prefix	char;
	procedure prt (x in varchar2) is
	begin
		dbms_output.put_line(x);
	end;
begin
	dbms_output.enable(1000000);
	prt('drop table Temp_Bean_usage;');
	prt('create table Temp_Bean_usage');
	prt(chr(9)||'(C_Bean_ID	number');
	for r in (select name from coffee_type order by id) loop
		prt(chr(9)||','||r.name||chr(9)||'char');
	end loop;
	prt(chr(9)||');');
	prt('insert into temp_bean_usage (C_Bean_ID)');
	prt(chr(9)||'select distinct c_Bean_ID from Coffee_mapping;');
	prt('commit;');
	for bean in (select distinct c_bean_id from coffee_mapping) loop
		prt ('update temp_bean_usage set');
		cnt	:= 0;
		for ctype in (select name 
				from coffee_type t,coffee_mapping m
				where t.id = m.coffee_type
				  and m.c_bean_id = bean.c_bean_id
				  order by name) loop
			cnt	:= cnt+1;
			if cnt = 1 then
				prefix	:= ' ';
			else
				prefix	:= ',';
			end if;
			prt(chr(9)||prefix||ctype.name||' = ''X''');
		end loop;
		prt(chr(9)||'where c_bean_id = '||bean.c_bean_id||';');
	end loop;
	prt ('commit;');
	for r in (select name from coffee_type order by id) loop
		prt('col '||r.name||' format a'||length(r.name));
	end loop;
	prt ('Select * from temp_bean_usage;');
end;
/
spool off
prompt
prompt Wrote script file "temp.sql" to client default directory
prompt

Section 4 - Image of the "temp.sql" script, which the above (Section 3) code generates:

Code:

drop table Temp_Bean_usage;
create table Temp_Bean_usage
   (C_Bean_ID      number
   ,Cappuchino     char
   ,Espresso       char
   ,Mocha  char
   ,Boring char
   );
insert into temp_bean_usage (C_Bean_ID)
   select distinct c_Bean_ID from Coffee_mapping;
commit;
update temp_bean_usage set
    Boring = 'X'
   ,Cappuchino = 'X'
   ,Espresso = 'X'
   ,Mocha = 'X'
   where c_bean_id = 1;
update temp_bean_usage set
    Mocha = 'X'
   where c_bean_id = 2;
update temp_bean_usage set
    Boring = 'X'
   ,Espresso = 'X'
   where c_bean_id = 3;
commit;
col Cappuchino format a10
col Espresso format a8
col Mocha format a5
col Boring format a6
Select * from temp_bean_usage;

Section 5 - Excecution of, and output from, generated "temp.sql" script:

Code:

SQL> @temp

 C_BEAN_ID CAPPUCHINO ESPRESSO MOCHA BORING
---------- ---------- -------- ----- ------
         1 X          X        X     X
         2                     X
         3            X              X

Let me know if this is satisfactory.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:43 (26Mar04) UTC (aka "GMT" and "Zulu"), 17:43 (25Mar04) Mountain Time)

uparna · Mar 26, 2004

Hi Dave ,
Excellent...Thank you.
I am not allowed to create temporary tables for this , and this has to be a report that has to be generated everytime a user clicks on the report link... i am not allowed to have report-specitic-tables or temporary tables in production for this....any other ideas to help me out of this dark alley?

Regards,
S. Jayaram Uparna .

SantaMufasa · Mar 26, 2004

Jayaram,

This reminds me of a card game we used to play as children. The game was called, "Carlotta". The only person that knew the rules of the game was the dealer and all the other players had to figure out the rules as we played. [wink]

Before I start making additional programming suggestions, let's get ALL the rules and restrictions on the table, okay?

So here are the rules that I've gleaned from your posts so far:

* The solution cannot create tables.
* The solution must re-generate for each invocation (i.e., nothing static).

Here are some additional questions I have which need to result in rules/specifications before proceeding:

* When you say, "this has to be a report that has to be generated everytime a user clicks on the report link", what is the final output device for users? Is it a printed report? Is it the screen?
* Is this a web-based application for which "html" an acceptable tool/language to produce results?
* What is truly the maximum number of "Coffee Types" that might appear on output?
* What is the maximum number of bean IDs?
* How often are "Coffee Types" and "C_Bean_IDs" modified (added, changed, deleted), thus changing the contents of the output?
* Are reports based upon user selection criteria, thus becoming tailored reports for each user?
* What is the response time that users expect?
* What does it mean that you are "not allowed to have report-specific-tables...in production for this"? Your "Coffee_Mapping" table (and I presume you have a "Coffee_Type" table) are mighty "report-specific" if you ask me. Can you not have a permanent summary table "in production" that looks like the one in my code above which changes (with perhaps a trigger) whenever the "Coffee_Type" and "Coffee_Mapping" tables change? (This would offer fastest possible speed with up-to-the-moment accuracy for your users.)

Let me/us know.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:51 (26Mar04) UTC (aka "GMT" and "Zulu"), 09:51 (26Mar04) Mountain Time)

uparna · Mar 26, 2004

Mufasa,

Oops, i had left too many loopholes there. I apologise.
Ok , Here goes:

Your assumption of the first 2 rules are right.

Q1."this has to be a report that has to be......."
A1.Screen. User looks at the report on the screen.

Q2."Is this a web-based application..."
A2. Yes it is. Java ( JSP , EJBs, XML et all) is used. As of now , for other reports , on click of the link , the app reads from a predesigned view and displayes the result set on the screen after formatting. I used HTML above only because i wanted to give a picture of how the o/p should look....and i didnt know how to draw tables in the comments in this forum.

Q3. "What is truly the maximum number of Cofee Types..."
A3.There are 100+ of them now and they are not going to significantly change over the next year.

Q4."What is the maximum number of bean IDs?"
A4.Theoretically, Unlimited. But practically , there may be around 10-20,000 of those in the first few months,. In the steady period , nearly 1500 is expected to be added montly.

Q5."Are reports based upon user selection criteria, thus becoming tailored reports for each user?"
A5. No , not that i know of.

Q6."What is the response time that users expect?"
A6.Yesterday.
But if this is something that is technically not feasible , then i can bully them into accepting a maximum of 30 secs time..

Q7."What does it mean that........"
A7.Well, some of the complex reports have necessitated us to use Stored procedures..... where in a procedure is invoked when the user clicks the report link. The procedure populates a "Report-table" (after computation). This table is a static table , and is frowned upon by the Production Database DBAs who will not allow this to happen. They are allergic to denormalised data in production.
The Coffee_Mapping table is a large transaction table with many columns , i have depicted only the 2 columns required for this report. Your assumption of the coffee_Type table is also right. The coffee_mapping table has data appended to it by the user all the time.

Thanks......i regret not having given all the details in the first shot, but then was afraid of scaring away my helpers with a loooooooong post.

Regards,
S. Jayaram Uparna .

SantaMufasa · Mar 26, 2004

Jayaram,

A couple of follow-up questions, then some suggestions:

Q1. What do you mean, "The coffee_mapping table has data appended to it by the user all the time"? Does "the user" mean "any user"? How frequently is "all the time"?

Q2. What is the difference to your DBAs between a "denormalized" table in the database versus a "denormalised" table in memory (i.e., a PL/SQL, or Java in-memory table) to gather data into the form you need?

If the "Production Database DBAs" refuse to allow the creation of a temporary/"denormalized" table, then I would go to them on bended knee, praising them for their greatness and nobility, and prevailing upon their pre-eminent goodness and wisdom and request that they propose a solution that meets with their standards. If they say, "I dunno, you figure it out," just explain that they cannot have it both ways: They cannot say "no" to you solution (just because it may involve denormalisation) and themselves refuse to propose a workable alternative.

So, as a stop-gap alternative, I propose you use the same technique that I did above, but instead of creating a temporary Oracle table, create the table in-memory using some form of Java dimension statement.

As a side comment: It appears to me that you and your DBAs find yourselves in somewhat of an adversarial relationship; that the DBAs are kings and masters of "their" domain; that whatever they say, goes. I am one of the first that will stand up and admit that "In Codd we Trust" and defend intelligent normalisation, but Codd would also admit that we can occasionally build a case for intelligent denormalisation. Unless your DBAs can propose a wise resolution for your need, then I suggest they allow a variance to their rules.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:27 (26Mar04) UTC (aka "GMT" and "Zulu"), 15:27 (26Mar04) Mountain Time)

uparna · Mar 29, 2004

Hi Mufasa,
Thats true...our Prod DBA's are the Mothership and we risk being cut off if we antagonise them.
I mean , they are nice guys and all that , but there is so much of red tape involved , so many unwritten rules that it can truly make a person lose his sanity, tear his shirt and run out of the building screaming and pulling out his hair.

However, we are the 'eternally compliant' group and they happen to love us and dote on us. I like the option of having another denormalised table for this ( we have a few in prod already , so i will have to explain this to them). I wanted to avoid asking them this and explaining it all over again until i was certain that there are no other efficient alternatives to it.

The Java dimension thing is something out of my reach, but i will mention it to my java Tech lead....and i am sure all the app people will froth at their mouths and cry 'Sacrilege!' at the mention of this.

.

Santa , i am really appreciative of all the cooperation that you have showed. Thanks... now i will go and prepare for the battle. Bless you!

Regards,
S. Jayaram Uparna .

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Transposing Rows as Columns for a Report

uparna

Technical User

SantaMufasa

Technical User

uparna

Technical User

SantaMufasa

Technical User

uparna

Technical User

SantaMufasa

Technical User

uparna

Technical User

Similar threads

Part and Inventory Search

Sponsor