Select that returns records in columns

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 23 Mar 2006 11:44 am

Hello guys!
I have a basic problem ... lol ... I need help from you guys.

I have a products table and a table operations.
the products table contains the record of the products table contains operations phases that this product to be manufactured.

In my select is returned as follows:
Code: Select all
Cod | Produto | Operacao   | Tempo
1   | Acerola | Moer       | 20
1   | Acerola | Filtrar    | 18
1   | Acerola | Concentrar | 17
I need to return as follows
Code: Select all
Cod | Produto | Operacao1 | Tempo1 |  Operacao2 | Tempo2 | ...
1   | Acerola | Moer      | 20     |  Filtrar   |  18    | ...
that is, the record (line) must be returned as the column.
Thank you for your attention.
CelsoBtu
celsobtu
Location: Botucatu - SP

Poston Thu, 23 Mar 2006 12:19 pm

Here's an example of a CROSSTAB:
Code: Select all
SQL> select * from emp order by hiredate;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

14 rows selected.

SQL>
SQL> select
  2  --  to_char(hiredate, 'yyyy/mm')
  3   deptno
  4  , sum(decode(to_char(hiredate, 'mm'), '12' ,sal ) )  a1980_12
  5  , sum(decode(to_char(hiredate, 'mm'), '01' ,sal ) )  a1981_01
  6  , sum(decode(to_char(hiredate, 'mm'), '02' ,sal ) )  a1981_02
  7  , sum(decode(to_char(hiredate, 'mm'), '04' ,sal ) )  a1981_04
  8  , sum(decode(to_char(hiredate, 'mm'), '05' ,sal ) )  a1981_05
  9  , sum(decode(to_char(hiredate, 'mm'), '06' ,sal ) )  a1981_06
10  from emp
11  group by
12    deptno
13  order by 1
14  /

    DEPTNO   A1980_12   A1981_01   A1981_02   A1981_04   A1981_05   A1981_06
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10                  1300                                        2450
        20       3800                             5975       1100
        30        950                  2850                  2850

SQL>
in your case, it is possible to do this, but has a maximum number of columns. Type, you can prepare your SQL to come 5 columns. (or 10 if applicable). But it is fixed!

Any questions, give me a ring.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 24 Mar 2006 3:27 pm

Thanks buddy! Thanks for the tip. worked perfect for my need.
Hugs, Celsobtu
celsobtu
Location: Botucatu - SP

Poston Tue, 17 Oct 2006 2:29 pm

:) Marvel ...

It's nice to know we have professionals and friends on this site ...
\"dr_gori\" valeu the hint ... I needed and it worked correctly Abs
fmarttos
Location: SP

Francisco Alves
Consultor Oracle


  • See also
    Replies
    Views
    Last Post