Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Jan 3rd, 2013

 

Using Pivot Table we can view data multidimensionally.
It is basically used for improved presentation of data.
A Pivot Table not only summarizes data by categories but also by subcategories.
One of the main advantages of Pivot Table is its ability to display trends spread over a range of categories.
Using Pivot Table we can transform the placement of rows and columns.

Consider we have a table which stores information such as production date, material name, material cost
This table holds historical data of cost of several materials for a certain period.
Now if we want to find out the total cost of each material for each and every date for all the materials at one go

We can use Pivot table query also know as cross tab query

Code
TRANSFORM Sum(Cost_Price) AS Cost


SELECT Format([Production_Date],"yyyy-mm-dd") AS [Production Date]


FROM materials_cost


GROUP BY Format([Production_Date],"yyyy-mm-dd")


PIVOT Material_Name;