I have a query which gives results like

ACTIVITY MONTH1 MONTH2 MONTH3 MONTH4 MONTH5 MONTH6 ...
PLAN 1.5 2.1 3.1 1.0 1.2 4.6
ACTUAL 1.2 2.O 2.9 0.7 1.0 2.7

I WANT TO CHANGE THE ORIENTATION OF THE TABLE IN THE FOR AS GIVEN BELOW,

MONTH PLAN ACTUAL

MONTH1 1.5 1.2
MONTH2 2.1 2.0
MONTH3 3.1 2.9
MONTH4 1.0 0.7

....

KINDLY HELP ME TO GET IT DONE AS I AM STUCK.

REGARDS,

MANOJ BISWAS

Hi,

Give your Table structure...

Regards
Veena

Actually it is a union query which is a rusult of 2-3 queries. This union query gives results like

ACTIVITY MONTH1 MONTH2 MONTH3 MONTH4 MONTH5 MONTH6 ... upto MONTH36

PLAN 1.5 2.1 3.1 1.0 1.2 4.6
ACTUAL 1.2 2.O 2.9 0.7 1.0 2.7

ACTIVITY, MONTH1, MONTH2 ETC is column of the query headings. and 1st row values are PLAN, 1.5, 2.1 etc and 2nd row values are ACTUAL, 1.2 2.0 etc.

I want to prepare a chart for monthwise progress for PLAN and ACTUAL values. For preparing the chart I need to realign the data in following manner. Ans so i need to write a query.

MONTH PLAN ACTUAL

MONTH1 1.5 1.2
MONTH2 2.1 2.0
MONTH3 3.1 2.9
MONTH4 1.0 0.7

etc.

Please help me to write a query for realigning the data as above.

Manoj Biswass

Hi,

I know..
But Create a Pivot (for Columns) and Decode the Pilot (for Rows) is an OverHead to the Database..
So, if you know table structure.. then writing query is Simple..

Assumin Table Structure to be :
MyDate MyPlan MyActual

Your query will be:

Select Format(MyDate,'mm-yyyy'), Sum(MyPlan), Sum(MyActual)  From MyTable Group By Format(MyDate,'mm-yyyy')

Regards
Veena

This article has been dead for over six months. Start a new discussion instead.