Good afternoon guru’s!
I’m hoping someone with Access & SQL experience (and time) would be able to write some SQL code that I could use in MS Access to transform/unpivot some data. I guess it’s a bit like a an unpivot.
Current table “tbl_Weekly_Forecast” is structured like this;
Item | A | B | C | D | ------------------------------- 61000224 | 1 | 2 | 3 | 4 | 64000123 | 25 | 25 | 25 | 25 | 66400005 | 38 | 38 | 37 | 37 |
Desired query output is like this;
Item | Period | Forecast | ------------------------------- 61000224 | A | 1 | 61000224 | B | 2 | 61000224 | C | 3 | 61000224 | D | 4 | 64000123 | A | 25 | 64000123 | B | 25 | 64000123 | C | 25 | 64000123 | D | 25 | 66400005 | A | 38 | 66400005 | B | 38 | 66400005 | C | 37 | 66400005 | D | 37 |
There are actually 24 columns to be unpivoted in table “tbl_Weekly_Forecast”, so I’m hoping I’ll be able to expand or add in the column names in to the SQL code and expand it myself.
I have spent about a day trying advice from others who have asked similar questions here, but unfortunately have very little SQL experience and have failed miserably so far. Any help would be most appreciated!
Thank you!
Advertisement
Answer
You can simply use UNION ALL as follows:
SELECT ITEM, 'A' PERIOD, A AS FORECAST FROM YOUR_TABLE UNION ALL SELECT ITEM, 'B' PERIOD, B AS FORECAST FROM YOUR_TABLE UNION ALL SELECT ITEM, 'C' PERIOD, C AS FORECAST FROM YOUR_TABLE UNION ALL SELECT ITEM, 'D' PERIOD, D AS FORECAST FROM YOUR_TABLE