Home » SQL & PL/SQL » SQL & PL/SQL » SQL query to return the value in columns (10 g)
SQL query to return the value in columns [message #320315] Wed, 14 May 2008 11:58 Go to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
All
I have a table which has three columns FirstName, LastName and
Class.

I want a query to display it like

Class1 Class2 .......... Classn
Number of rows for each Class in the table.

I tried the following but I have to hard code the values in Class Column, I don't want to do that.

select
sum(decode (class,'Class1',1,nvl)) as Class1
...
sum(decode (class,'Classn',1,nvl)) as Class1
from tableA

Instead of harcoding the values of Class column, is there a way to get the values of class column from table.

Please help....


[Updated on: Wed, 14 May 2008 11:59]

Report message to a moderator

Re: SQL query to return the value in columns [message #320316 is a reply to message #320315] Wed, 14 May 2008 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
search this forum for "pivot query"
Re: SQL query to return the value in columns [message #320318 is a reply to message #320315] Wed, 14 May 2008 12:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
try this:

Kevin Meade's OraFAQ Blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

Kevin
Re: SQL query to return the value in columns [message #320357 is a reply to message #320315] Wed, 14 May 2008 17:07 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
Pivot query is good if I know the values in class column, and hardcode it in decode or case. I donot know the values in class column, the columns returned can be different. Please help me on that.
Re: SQL query to return the value in columns [message #320359 is a reply to message #320315] Wed, 14 May 2008 17:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above.

Since you are a Senior Member, You're On Your Own (YOYO)!
Re: SQL query to return the value in columns [message #320361 is a reply to message #320315] Wed, 14 May 2008 17:29 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
I didn't get which one I am violating??
Re: SQL query to return the value in columns [message #320365 is a reply to message #320315] Wed, 14 May 2008 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Practice

Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want.

Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. 
It'll help people setting up a test set (yes, some people really do that)

Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.

Provide your expected result set and explain the rules/reasons that lead to it.

[Updated on: Wed, 14 May 2008 17:52] by Moderator

Report message to a moderator

Re: SQL query to return the value in columns [message #320367 is a reply to message #320315] Wed, 14 May 2008 18:29 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
create table tableA
(firstname varchar2(20),
lastname varchar2(20),
class varchar2(10));

insert into tableA values('A','A','ClassA');
insert into tableA values('B','B','ClassB');
insert into tableA values('C','C','ClassC');
insert into tableA values('A','B','ClassA');
insert into tableA values('B','A','ClassB');
insert into tableA values('C','A','ClassC');

if I do the following

select class,count(*) from tableA
group by class;

I get the following
ClassA 2
ClassB 2
ClassC 2

but I want the output like

ClassA ClassB ClassC
2 2 2

Now I can use decode or case if I know the distinct values in Class Column. But I donot know the distinct values in this column. It may change every month. I want this automated process, and it gets distinct values and display as abhove. Hope this explains my issue.




Re: SQL query to return the value in columns [message #320370 is a reply to message #320315] Wed, 14 May 2008 19:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you read the link I gave you above about pivots, you will see at the end of other people's comments, there is reference to a TOM KYTE article that does exactly what you want.

It pivots an arbitrary number of unknown values.

Do the reading, then get back to us.

Good luck, Kevin
Re: SQL query to return the value in columns [message #320403 is a reply to message #320357] Thu, 15 May 2008 00:35 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already post solutions of this here, on AskTom and many other places.
For instance,
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:766825833740#75611182455045
(create table and insert statements are at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:766825833740#75591293704557)

Regards
Michel

Previous Topic: I Have object problem on oracle 9i?
Next Topic: How to fetch data from database in pdf format?
Goto Forum:
  


Current Time: Fri Dec 09 23:02:13 CST 2016

Total time taken to generate the page: 0.24422 seconds