Create Pivot or crosstab data from flat table – Consumer price index dashboard

Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on email

In the previous section we built a simple dashboard using the CPI data.

In this part, we will use some pivot SQL queries to facilitate comparison of multiple categories on the same chart.

The charts require the data to be in certain format. It expects the first column to be the x-axis and the following columns as the y-axis values.

In case of multiple y-axis like stack bar or group column chart or multi line chart, we simply provide the values as columns

Image

 

If we want to display, say coffee and milk on the same line chart then our column1 should represent coffee and column2 should be milk and so on.

But as we now our data is not in this format. It is instead stacked one below another like this

Image

So we will use some SQL tricks to pivot the data

Image

We use the SQL as below

select *
from
(
select yr
,max(steak) steak
,max(bacon) bacon
,max(pork) pork
,max(gasoline) gas
,max(Margarine) Margarine
,max(Bread) Bread
,max(Butter) butter
,max(Eggs) eggs
,max(Beans) beans
,max(Milk) milk
,max(Coffee) coffee
,max(Sugar) sugar
,max(Potatoes) potatoes
,max(Rice) rice
,max(Flour) flour
from
(
select yr,item_category,
case when item_category=’Steak’ then average_annual_pricevalue
else 0
end steak
,
case when item_category=’Bacon’ then average_annual_pricevalue
else 0
end Bacon
,
case when item_category=’Pork Chops’ then average_annual_pricevalue
else 0
end pork
,
case when item_category=’Gasoline’ then average_annual_pricevalue
else 0
end gasoline
,
case when item_category=’Margarine’ then average_annual_pricevalue
else 0
end Margarine
,
case when item_category=’Bread’ then average_annual_pricevalue
else 0
end Bread
,
case when item_category=’Butter’ then average_annual_pricevalue
else 0
end Butter
,
case when item_category=’Eggs’ then average_annual_pricevalue
else 0
end Eggs
,
case when item_category=’Beans’ then average_annual_pricevalue
else 0
end Beans
,
case when item_category=’Milk’ then average_annual_pricevalue
else 0
end Milk
,
case when item_category=’Coffee’ then average_annual_pricevalue
else 0
end Coffee
,
case when item_category=’Sugar’ then average_annual_pricevalue
else 0
end Sugar
,
case when item_category=’Potatoes’ then average_annual_pricevalue
else 0
end Potatoes
,
case when item_category=’Rice’ then average_annual_pricevalue
else 0
end Rice
,
case when item_category=’Flour’ then average_annual_pricevalue
else 0
end Flour
from inflation
where item_category in
(‘Steak’,’Bacon’,’Pork Chops’,’Gasoline’,’Margarine’,’Bread’,
‘Butter’,’Eggs’,’Beans’,’Milk’,’Coffee’,’Sugar’,’Potatoes’,’Rice’,’Flour’
)
) as x
group by yr
order by yr asc
) as y

 

We use the case statement and combination of max and group functions to get the desired pivot table.

We then use selected columns to create a multi line chart.

select yr, steak,bacon,pork,gas
from
(
select yr,max(steak) steak,max(bacon) bacon,max(pork) pork,max(gasoline) gas,
max(Margarine) Margarine
,max(Bread) Bread
,max(Butter) butter
,max(Eggs) eggs
,max(Beans) beans
,max(Milk) milk
,max(Coffee) coffee
,max(Sugar) sugar
,max(Potatoes) potatoes
,max(Rice) rice
,max(Flour) flour
from
(
select yr,item_category,
case when item_category=’Steak’ then average_annual_pricevalue
else 0
end steak
,
case when item_category=’Bacon’ then average_annual_pricevalue
else 0
end Bacon
,
case when item_category=’Pork Chops’ then average_annual_pricevalue
else 0
end pork
,
case when item_category=’Gasoline’ then average_annual_pricevalue
else 0
end gasoline
,
case when item_category=’Margarine’ then average_annual_pricevalue
else 0
end Margarine
,
case when item_category=’Bread’ then average_annual_pricevalue
else 0
end Bread
,
case when item_category=’Butter’ then average_annual_pricevalue
else 0
end Butter
,
case when item_category=’Eggs’ then average_annual_pricevalue
else 0
end Eggs
,
case when item_category=’Beans’ then average_annual_pricevalue
else 0
end Beans
,
case when item_category=’Milk’ then average_annual_pricevalue
else 0
end Milk
,
case when item_category=’Coffee’ then average_annual_pricevalue
else 0
end Coffee
,
case when item_category=’Sugar’ then average_annual_pricevalue
else 0
end Sugar
,
case when item_category=’Potatoes’ then average_annual_pricevalue
else 0
end Potatoes
,
case when item_category=’Rice’ then average_annual_pricevalue
else 0
end Rice
,
case when item_category=’Flour’ then average_annual_pricevalue
else 0
end Flour
from inflation
where item_category in
(‘Steak’,’Bacon’,’Pork Chops’,’Gasoline’,’Margarine’,’Bread’,
‘Butter’,’Eggs’,’Beans’,’Milk’,’Coffee’,’Sugar’,’Potatoes’,’Rice’,’Flour’
)
) as x
group by yr
order by yr asc
) as y

The above SQL in the chart gives the below result

Image

Some quirks, we can provide the line colors and freeze them so that it does not change

Edit Chart properties and modify the following properties

colors=[“#FFDB4D”,”#84C443″,”#49C4B4″,”#4955C4″,”#974AC4″,”#C44CA0″,”#C4525A”]

colorfreeze=true

key=[“steak”,”bacon”,”pork”,”gas”]

title=”steak – bacon – pork – gas”

Similarly you can duplicate and create another comparison between other item categories.

Here is the final dashboard : Consumer Price Index Dashboard

Here is the first part of this dashboard tutorial

The Core Tools

Create dashboard for any Database

Data Visualizer and Dashboard Application
SALE
This is the best dashboard software for its price. One good thing we did was to hire their consulting services to build few dashboard prototypes and provide some quick dashboard training.
- Terry Seal, IL
We evaluated Xcelsius and Qlikview and the cost for organization to implement dashboards was quoted over 10,000 USD. For fraction of the above quoted price, we were able to buy the licenses for the web based dashboard software and get some free training. This is truly a dashboard software for small businesses like us.
IT Manager of a Trucking company, OH