12 columns of months = 1 column of values + 1 column of month

Question

12 columns of months = 1 column of values + 1 column of month

Navigation

#1 by (0 votes)

0

I have a table with SQL binding (PERVASIVE) with one column per month, that is, 12. In order to better work the data, I need to transform the 12 columns into only 2 ... a 1ª with the values that were in the 12 columns and a 2ª column with the respective month, that is, what was before the title of the columns .

An example of the current table:

Al_Cta     | MoedCod | TpVal   | FlagDC  | Month01 | Month02 | Month03
AAAAA      |     100 |      20 |        5|      15 |      18 |      16
BBBBB      |     200 |      40 |       10|      20 |      21 |      26
CCCCC      |     300 |      60 |       15|      40 |      48 |      41

And I want to:

Al_Cta     |  MoedCod|TpVal | FlagDC  | Value   | Month
AAAAA      |     100 |    20|       5 |      15 |     01
AAAAA      |     100 |    20|       5 |      18 |     02
AAAAA      |     100 |    20|       5 |      16 |     03
BBBBB      |     200 |    40|      10 |      20 |     01
BBBBB      |     200 |    40|      10 |      21 |     02
BBBBB      |     200 |    40|      10 |      26 |     03
CCCCC      |     300 |    60|      15 |      40 |     01
CCCCC      |     300 |    60|      15 |      48 |     02
CCCCC      |     300 |    60|      15 |      41 |     03

Thank you

sql

asked by anonymous 14.03.2017 / 00:55

1 answer

PHP display duplicate record java.lang.NullPointerException error while trying to popular Spinner

score 0 · Answer 1

Have you tried using UNPIVOT?

CREATE TABLE tbl_teste (Al_Cta varchar(10), MoedCod int, TpVal int, FlagDC int, Month01 int, Month02 int, Month03 int);

INSERT INTO tbl_teste VALUES ('AAAAA', 100, 20, 5, 15, 18, 16);
INSERT INTO tbl_teste VALUES ('BBBBB', 200, 40, 10, 20, 21, 26);
INSERT INTO tbl_teste VALUES ('CCCCC', 300, 60, 15, 40, 48, 41);


SELECT Al_Cta, MoedCod, TpVal, FlagDC, mes, valor
FROM 
    (SELECT Al_Cta, MoedCod, TpVal, FlagDC, Month01, Month02, Month03 FROM tbl_teste) p
UNPIVOT
    (valor FOR Mes IN
        (Month01, Month02, Month03)
    )AS UNPIVOT_tbl_teste