[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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.
0
digs developer
Asked:
digs developer
1 Solution
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now