Transform column line content

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 93
Joined: Tue, 06 Apr 2010 11:31 am
Location: São Paulo - SP

Good gentlemen, I have the result of a query where I would like to transform the column content into new columns. In the example below, I would like to transform the column group into columns (group1, group2 ...)
can I do this without creating temporary table and sps?

Date Group Item Total
01/07/2017 Group 1 Nail 1
01/07/2017 Group 2 Screw 5
01/07 / 2017 Group 2 Package 1 6
02/07/2017 Group 1 Package 2 8
02/07/2017 Group 2 Screw 6
02/07/2017 Group 2 Location 4
02/07/2017 Group 3 Package 1 8
03/07/2017 Group 1 Screw 12
03/07/2017 Group 4 Location 12
Renato Menezes Viana
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 128
Joined: Sat, 18 Nov 2006 11:51 am
Location: Rio de Janeiro - RJ

Colleague, note the link below:
http://www.oracle.com/technetwork/pt/ar ... 3-ptb.html
ABS
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Hello,

If you are using version prior to 11 (where has been included the Pivot and Unpivot function), you can do as follows:

Select all

WITH MYSQL AS ( 
SELECT '01/07/2017' DT_X, 'Grupo 1' GRUPO, 'prego'     ITEM ,1     QTDE FROM DUAL UNION ALL 
SELECT '01/07/2017' DT_X, 'Grupo 2' GRUPO, 'parafuso'   ITEM ,5     QTDE FROM DUAL UNION ALL 
SELECT '01/07/2017' DT_X, 'Grupo 2' GRUPO, 'pacote 1'   ITEM ,6     QTDE FROM DUAL UNION ALL 
SELECT '02/07/2017' DT_X, 'Grupo 1' GRUPO, 'pacote 2'   ITEM ,8     QTDE FROM DUAL UNION ALL 
SELECT '02/07/2017' DT_X, 'Grupo 2' GRUPO, 'parafuso'   ITEM ,6     QTDE FROM DUAL UNION ALL 
SELECT '02/07/2017' DT_X, 'Grupo 2' GRUPO, 'local'     ITEM ,4     QTDE FROM DUAL UNION ALL 
SELECT '02/07/2017' DT_X, 'Grupo 3' GRUPO, 'pacote 1' 	ITEM ,8     QTDE FROM DUAL UNION ALL 
SELECT '03/07/2017' DT_X, 'Grupo 1' GRUPO, 'parafuso' 	ITEM ,12    QTDE FROM DUAL UNION ALL 
SELECT '03/07/2017' DT_X, 'Grupo 4' GRUPO, 'local' 		ITEM ,12    QTDE FROM DUAL) 
 
SELECT M.DT_X 
     , M.ITEM 
     , SUM(CASE WHEN M.GRUPO = 'Grupo 1' THEN M.QTDE ELSE 0 END) GRUPO1 
     , SUM(CASE WHEN M.GRUPO = 'Grupo 2' THEN M.QTDE ELSE 0 END) GRUPO2 
     , SUM(CASE WHEN M.GRUPO = 'Grupo 3' THEN M.QTDE ELSE 0 END) GRUPO3 
     , SUM(CASE WHEN M.GRUPO = 'Grupo 4' THEN M.QTDE ELSE 0 END) GRUPO4      
  FROM MYSQL M 
 GROUP BY M.DT_X 
     , M.ITEM


Note: Considering that the value you want to group in each group is the amount of items.
Obs2: You must manually specify each group that exists.
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 93
Joined: Tue, 06 Apr 2010 11:31 am
Location: São Paulo - SP

Sirs Thanks for the return ...
The two forms presented functioned ...
Thanks
Att
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests