using data as column header [message #20086] |
Mon, 29 April 2002 19:46 |
Andrew Rosenberg
Messages: 5 Registered: April 2002
|
Junior Member |
|
|
I don't think this is a terribly complex problem, but i can't seem to figure it out. I originally posted this message on the newbie board but have gotten no responses since last week.
I have data that looks like this:
col1 col2 total
---- ---- -----
a A 1
a B 3
b A 2
b B 1
I'd like a report that displays this as
col1 A B
---- - -
a 1 3
b 2 1
any advice is welcome. My apologies for taking your time on an issue that may not be as complex as this board is meant for.
thanks in advance,
Andrew
|
|
|
Re: using data as column header [message #20096 is a reply to message #20086] |
Tue, 30 April 2002 01:43 |
Shankar. V
Messages: 5 Registered: April 2002
|
Junior Member |
|
|
Mr. Andrew,
I have created a table based on your table as below and then I added the sample data as you given:
create table demo (
col1 varchar2(3),
col2 varchar2(3),
total number);
Query:
******
select col1,
sum(decode(col2,'A',total)) A,
sum(decode(col2,'B',total)) B
from demo group by col1;
I hope this serves the purpose.
|
|
|
|
Re: using data as column header - respecification [message #20121 is a reply to message #20086] |
Tue, 30 April 2002 06:39 |
Andrew Rosenberg
Messages: 5 Registered: April 2002
|
Junior Member |
|
|
Thank both of you for your help, but my problem is slightly more complex, as i need the column names to be dynamically generated, based on the table data.
the data could look like
col1 col2 total
---- ---- -----
a A 1
b A 3
a B 3
b B 4
or
col1 col2 total
---- ---- -----
a B 1
a B 3
a D 3
a D 4
i need to display the previous as:
col1 A B
---- - -
a 1 3
b 3 4
and the latter as
col1 B D
---- - -
a 1 3
b 3 4
essentially i need to convert the data in col2 to the column header for the report.
thank you again
-andrew
|
|
|