DOUBT IN SQL -TURN ROWS INTO COLUMNS

Question

I got this one query, it brings to the same product 3 rows of result, wanted these results to be returned in columns, how to do?

SELECT CODPROD, EMB 
FROM TABELA
WHERE PRODUTO = 2

this research brings the following:

CODPROD | EMBALAGEM
      2 |       1X1
      2 |       1X2
      2 |       1X3

I wish you’d bring it like this:

      2 | 1X1 | 1X2 | 1X3

Answer 1

If the version of yours Oracle really is the 11g use the function LISTAGG:

SELECT CODPROD,
       LISTAGG(EMB, ', ') WITHIN GROUP (ORDER BY EMB) AS EMBALAGEM
  FROM TABELA
 WHERE PRODUTO = 2
 GROUP BY CODPROD

---

LISTAGG

For a specified Measure, LISTAGG Orders data Within each group specified in the ORDER BY clause and then concatenates the values of the Measure column.

In free translation:

For a specified column, the LISTAGG sorts the data within each group specified in the clause ORDER BY and concatenates the column values.

Answer 2

Use the function PIVOT to bring your rows in columns:

Example:

SELECT *
  FROM (SELECT 1 codigo, 'Carro' nome FROM dual UNION ALL 
        SELECT 2 codigo, 'Moto' nome FROM dual UNION ALL 
        SELECT 3 codigo, 'Avião' nome FROM dual )    
PIVOT
(
 MAX(codigo) FOR nome IN ('Carro' Carro,'Moto' Moto,'Avião' Avião)
)

Upshot:

see rotating on SQL Fiddle.

More information on the subject can be found here.

A more advanced option would be to create a function that runs the SQL dynamically and returns a CURSOR, when you don’t know the values that can return...

Follow an example:

CREATE OR REPLACE FUNCTION fun_linhas_para_tabelas RETURN SYS_REFCURSOR IS

  sqlqry CLOB;
  cols   CLOB;
  TYPE t_ref_cursor IS REF CURSOR;
  c t_ref_cursor;

BEGIN

  SELECT listagg('''' || nome || ''' as "' || nome || '"', ',') within GROUP(ORDER BY nome)
    INTO cols
    FROM (SELECT 1 codigo, 'Carro' nome
            FROM dual
          UNION ALL
          SELECT 2 codigo, 'Moto' nome
            FROM dual
          UNION ALL
          SELECT 3 codigo, 'Avião' nome
            FROM dual);

  sqlqry := 'select * from
             (
                SELECT 1 codigo, ''Carro'' nome FROM dual UNION ALL
                SELECT 2 codigo, ''Moto'' nome FROM dual UNION ALL
                SELECT 3 codigo, ''Avião'' nome FROM dual
             )
             pivot
             (
               Max(codigo) for nome in (' || cols || ')
             )';


  OPEN c FOR sqlqry;
  RETURN c;

END;

Answer 3

You need to use the PIVOT, it will turn rows into columns, for example:

Select CODPROD
         , [1] as coluna_um
         , [2] as coluna_dois
         , [3] as coluna_tres
from TABELA pivot (Embalagem for Emb ([1],[2],[3])) p
order by 1;

You will have to have 3 values, one for the first column, in case the CODPROD, other for the other columns in the case of EMB, and the other to bring the value of CODPROD x EMB, in the case I believe it is the PACKING.