Need oracle sql query

Hello experts ,

Oracle sql query


DATE     TYPE     CONUTNO
1-JAN   ABCTYPE   2
1-JAN   DEFTYPE   5
1-JAN   YYYTYPE   2
2-JAN   ABCTYPE   9
2-JAN   DEFTYPE   5
2-JAN   YYYTYPE   2

I want below output

date   ABCTYPE   DEFTYPE   YYYTYPE    
1-JAN   2           5        2
2-JAN   9           5        2


TYPE VALUES MAY BE LESS OR MORE.


Please help.
digs developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>TYPE VALUES MAY BE LESS OR MORE.

You need to know the maximum number allowed.  You cannot dynamically create 'columns' as the number of rows in the table varies.
0
sdstuberCommented:
you can't have dynamic columns in sql,  the number, type and names of the columns must be known at parse time


SELECT yourdate,
max(case when type = 'ABCTYPE' then conutno end) ABCTYPE,
max(case when type = 'DEFTYPE' then conutno end) DEFTYPE,
max(case when type = 'YYYTYPE' then conutno end) YYYTYPE
  FROM yourdata
  group by yourdate
0
slightwv (䄆 Netminder) Commented:
A possible alternative to you MAX with GROUP BY above is using PIVOT.

Not sure which one will be better against your data.

select *
from yourtable
	pivot ( sum(conutno) count
		for mytype in (
			'ABCTYPE' as abctype,
			'DEFTYPE' as deftype,
			'YYYTYPE' as yyytype
		)
	)
/

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

digs developerAuthor Commented:
thank you !!

But we are not sure the type values some times the values are more upto 10.
Then how can we do that ?
Apart fromhardcode values any other solution.

please help
0
slightwv (䄆 Netminder) Commented:
>>Apart fromhardcode values any other solution.

Not using straight SQL.

You could probably write PL/SQL to build dynamic SQL but that would be messy.

I guess a lot depends what how you need the results.

A cursor passed back from a procedure, spooled out from sqlplus, what???
0
digs developerAuthor Commented:
Java peoples want a query itself.
0
slightwv (䄆 Netminder) Commented:
If they want the raw select statement as 'text' to plug into their application and it just runs, then I don't think you can get there from here.

Time to rethink the plan.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
I'd also suggest to try built-in pivoting and/or analytic functions to solve this, but you may also want to give "method 4 dynamic sql in pl/sql" a try ;-)
http://www.oracle-developer.net/display.php?id=422

This can become quite handy in your case (variable column count). But remember: most people (including me) would consider this as some kind of workaround and you should be very cautious due to its dynamic nature considering performance issues for instance ('cause the CBO might be out of luck)...
0
Mark GeerlingsDatabase AdministratorCommented:
Basically, what you are asking for is a "matrix" or "cross-tab" output from a query.  Simple SQL queries have never been able to to produce these, at least not without using CASE or DECODE and hard-coding the query for every possible value.  Reporting tools like Oracle Reports and Crystal Reports were used historically to produce this kind of output.  Some of the suggestions from others here should get you close to what you are looking for.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.