Normalize database (unpivot columns per month)

Asked

Viewed 45 times

1

Good night,

An Excel table with PERVASIVE SQL connection with one column per month, that is, 12 columns. In order to better work the data, I need to transform the 12 columns into only 2: - a first with the values that were in the 12 columns - a second column with its month, that is to say, the one before the column heading.

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 excel

  • you need a macro to generate another spreadsheet "normalized"?

  • https://support.office.com/pt-br/article/Transformar-colunas-Power-Query-em-linhas-0f7bad4b-9ea1-49c1-9d95-f588221c7098

No answers

Browser other questions tagged sql excel

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