Usually people want to do the opposite... but I would like to take 12 period columns and transform them into separate rows.
So currently after my SQL statement (it sums item sales for a specific year and class) runs, I have the following columns in one resulting row:
Year, Period, Class, SalesQty1, Sales$1, SalesQty2, Sales$2, SalesQty3, Sales$3, ... , SalesQty12, Sales$12
I would like 12 rows instead that look like this:
Year, Period, Class, SalesQty1, Sales$1
Year, Period, Class, SalesQty2, Sales$2
Year, Period, Class, SalesQty3, Sales$3
...
Year, Period, Class, SalesQty12, Sales$12
My SQL statement is currently something like:
How to do this transform within my SQL statement...? TIA.
So currently after my SQL statement (it sums item sales for a specific year and class) runs, I have the following columns in one resulting row:
Year, Period, Class, SalesQty1, Sales$1, SalesQty2, Sales$2, SalesQty3, Sales$3, ... , SalesQty12, Sales$12
I would like 12 rows instead that look like this:
Year, Period, Class, SalesQty1, Sales$1
Year, Period, Class, SalesQty2, Sales$2
Year, Period, Class, SalesQty3, Sales$3
...
Year, Period, Class, SalesQty12, Sales$12
My SQL statement is currently something like:
Code:
select max(GCLS) as Class, max(GFY) as Year, sum(GQ01) as SalesQty1, sum(GA01/100) as Sales$1, sum(GQ02) as SalesQty2, sum(GA01/100) as Sales$2, sum(GQ03) as SalesQty3, sum(GA01/100) as Sales$3, sum(GQ04) as SalesQty4, sum(GA01/100) as Sales$4, sum(GQ05) as SalesQty5, sum(GA01/100) as Sales$5, sum(GQ06) as SalesQty6, sum(GA01/100) as Sales$6, sum(GQ07) as SalesQty7, sum(GA01/100) as Sales$7, sum(GQ08) as SalesQty8, sum(GA01/100) as Sales$8, sum(GQ09) as SalesQty9, sum(GA01/100) as Sales$9, sum(GQ10) as SalesQty10, sum(GA01/100) as Sales$10, sum(GQ11) as SalesQty11, sum(GA01/100) as Sales$11, sum(GQ12) as SalesQty12, sum(GA01/100) as Sales$12 from myFile where GCLS = :myclass and GFY = :myYear
Comment