Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Normalize database (unpivot month columns)

Status
Not open for further replies.

deathseeker

Technical User
Mar 28, 2017
1
PT
I have a Excel table connected to SQL(PERVASIVE) that outputs a column for each month.
For working it better in Excel, I need to transforme that 12 columns to only 2...
- a first one for the values;
- the second for the respective month (that at this moment, months are the column titles).


Objective: turn the current database structure:

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

The desirable output:

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




I already had some help in the forum, but I did not managed to do it.
I use the following command, but the database simply do not change at all, could someone see what is wrong?
I am inserting the following in command text, in query properties on Excel.

----------

SELECT *
FROM [IN16-CTAVAL]
Select p.Al_Cta ,MoedCod ,TpVal,FlagDC,
sale_quantity ,salemonths
From products as p
inner join (
Select Al_Cta ,sale_quantity ,salemonths from products
UNPIVOT
(
salemonths for sale_quantity in (Month01 | Month02 | Month03)
)) as pp on p.Al_Cta = pp.Al_Cta

----------


Best regards!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top