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
Transform column line content
-
- Rank: Analista Pleno
- Posts: 128
- Joined: Sat, 18 Nov 2006 11:51 am
- Location: Rio de Janeiro - RJ
-
- Moderador
- Posts: 641
- Joined: Mon, 03 Sep 2007 3:26 pm
- Location: Fortaleza - CE
att,
Daniel N.N.
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:
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.
If you are using version prior to 11 (where has been included the Pivot and Unpivot function), you can do as follows:
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.
-
- Information
-
Who is online
Users browsing this forum: No registered users and 15 guests