12 month columns => 1 value column + 1 month column

Asked

Viewed 247 times

0

I have a table with SQL connection (PERVASIVE) with a column for each month, that is, 12. In order to better work the data, I need to transform the 12 columns in only 2... a 1st with the values that were in the 12 columns and a 2nd column with the respective month, that is, what was before the column title.

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

1 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

  • Thank you. You have to use UNPIVOT, but I’m not getting the SQL command right. In this command, are you manually setting the values? (from 2nd to 4th line) Is that this database is only one example, the real has thousands of lines.

  • It was just to create a test table with some values, just adapt to your original table. To test, put in a temporary about 50 records.

  • I copied and pasted in Command Text, but received the message that it is not supported. :(

  • In create, swap the int for integer, see if it works. I don’t know exactly how you’re running the command.

  • Same problem, not supported. :(

  • Try to run straight on your table, no need to create one to test, just format for your.

Show 1 more comment

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.