Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Quick query help

user11934912Aug 27 2010 — edited Aug 27 2010
Hello, I have a table:

Model | Make
------------------------
Camry | TOYOTA
Civic | HONDA
Corolla | TOYOTA
Accord | HONDA


I want the result like so:

Cars
---------
---HONDA---
Accord
Civic
---TOYOTA---
Camry
Corolla


Thanks in advance.

Edited by: user11934912 on Aug 27, 2010 10:25 AM

Comments

663214
select model, make from table_car
group by make, model
user11934912
No, I don't want two columns. I only want one.

I want it to be ordered by the make field first, with the make listed followed by the models for that specific make.
513949
One possible solution:
SQL> with cars as
    (select 'Camry' as Model, 'TOYOTA'as Make from dual union all
     select 'Civic' as Model, 'HONDA'as Make from dual union all
     select 'Corolla' as Model, 'TOYOTA'as Make from dual union all
     select 'Accord' as Model, 'HONDA'as Make from dual)
    select nvl(c2, c1)
    from
    (select c1, c2 
    from (select distinct(make) c1, null c2 from cars
          union all
          select make c1, model c2 from cars)
    order by c1, c2 nulls first);

NVL(C2,
-------
HONDA
Accord
Civic
TOYOTA
Camry
Corolla

6 rows selected.
[]s,
Miguel
Frank Kulash
Hi,

Use GROUP BY ROLLUP .
Since you didn't post versions of your table, I'll use scott.emp to illustrate:
SELECT	  CASE
		WHEN  grouping (sal) = 0
		THEN  sal
		ELSE  deptno
 	  END 	      AS sal_or_deptno
FROM	  scott.emp
GROUP BY  ROLLUP ( deptno
      	  	 , sal
		 )
ORDER BY   deptno
,     	   GROUPING (sal)	DESC
,	   sal
;
Output:
AL_OR_DEPTNO
-------------
           10
         1300
         2450
         5000
           20
          800
         1100
         2975
         3000
           30
          950
         1250
         1500
         1600
         2850
The department numbers are 2-digit numbers. Each one is followed by the sals (always 3 or more digits) in that department.
user11934912
Thats a fantastic solution. Thank you sir.
513949
So, set the thread as Answered. It helps other users.
Aketi Jyuuzou
I like grouping :D
with cars as
(select 'Camry' as Model, 'TOYOTA'as Make from dual union
 select 'Civic' as Model, 'HONDA'as Make from dual union
 select 'Corolla' as Model, 'TOYOTA'as Make from dual union
 select 'Accord' as Model, 'HONDA'as Make from dual)
select case grouping(Model) when 1 then Make else Model end
  from cars
group by rollup(Model),Make
order by Make,grouping(Model) desc;

CASEGRO
-------
HONDA
Accord
Civic
TOYOTA
Camry
Corolla
BelMan
select mode||'----'|| make from table_car
group by make, model
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 24 2010
Added on Aug 27 2010
8 comments
1,260 views