In this example, cross-tabulation, or crosstab, on financial accounting of a company is considered.On financial accounting of a company, journal voucher is a unit that manages financial statement of a company.<\/p>\n\n\n\n
In SQL, journal voucher can be considered as a table entry. That is , journal voucher table is composed of following columns; “ID”, “name of an account item”, “price of the item” and “transaction date”<\/em>. Journal voucher table may contain a flag or type column that shows an item is recorded as credit, deposit, payment, and so on. The structure of journal voucher table can be figured as follow;<\/p>\n\n\n\n For example,\u00a0 an entry of the above journal voucher table is recorded as “10,\u00a02019-04-01, 1,000 JPY, traveling fee, expense payment”<\/strong> , that means “Voucher ID 10: traveling fee\u00a0 1,000 JPY was paid as expense payment in April 1st in 2019”<\/strong>.<\/p>\n\n\n\n Cross-tabulation, or crosstab query can calculate monthly total from such journal voucher table. In other words, crosstab query of monthly total converts a table such as<\/p>\n\n\n\n to another table like<\/p>\n\n\n\n Crosstab query of monthly total in an JP fiscal year can be described as follow:<\/p>\n\n\n\n The procedure of this crosstab query is<\/p>\n\n\n\n In Japan, fiscal year is composed of 2Q, 3Q, 4Q and 1Q of next year. The SELECT query labeled “jpfiscal” can be written by “case” and “having”, that The format of “case” sentence is as follow:<\/p>\n\n\n\n If condition is True, action1 is extecuted, else, action2 is executed.<\/p>\n\n\n\n Suppose, “vhrtransfer” table contains columns as follow:<\/p>\n\n\n\n “vhrtransfer” table contains “isCredit” column. If the isCredit flag is set, the account item is recorded as credit. <\/p>\n\n\n\n A sorted list for jpfiscal sub query should be created from “vhrtransfer” table by pulling such entries whose isCredit column is set as 0. The SQL query for creating “sortedlist” is as follow:<\/p>\n\n\n\n Here, the table created by jpfiscal sub query would be calculated and sorted as a crosstab table. The image of crosstab table is as follow: <\/p>\n\n\n\n Pseudo SQL query for crosstab output of monthly total can be written as follow:<\/p>\n\n\n\n Thus, the sum of each month should be calculated from each entry of the table generated by sub queries.<\/p>\n\n\n\n As an example of the case sentence of each month, the query for sum in April would be described as follow:<\/p>\n\n\n\n this sum calculation query means:<\/p>\n\n\n\n When the timestamp of an entry is in April, calculate sum of prices on the entry and use the sum of prices as a column, that is labeled as April. If the month of the timestamp is not April, add 0. <\/p><\/blockquote>\n\n\n\n Crosstab output query of monthly total would be made by writing such sum calculation query for each month.<\/p>\n","protected":false},"excerpt":{"rendered":"ID<\/th> Date<\/th> Price<\/th> Item<\/th> Type<\/th><\/tr><\/thead><\/table>\n\n\n\n ID<\/th> Date<\/th> Price<\/th> Item<\/th> Type<\/th><\/tr><\/thead><\/table>\n\n\n\n Item<\/th> April<\/th> May<\/th> June<\/th> …<\/th> March<\/th><\/tr><\/thead><\/table>\n\n\n\n Crosstab query<\/h1>\n\n\n\n
select item,
sum(case when m='04' then price else 0 end) as April,
sum(case when m='05' then price else 0 end) as May,
sum(case when m='06' then price else 0 end) as June,
sum(case when m='07' then price else 0 end) as July,
sum(case when m='08' then price else 0 end) as August,
sum(case when m='09' then price else 0 end) as September,
sum(case when m='10' then price else 0 end) as October,
sum(case when m='11' then price else 0 end) as November,
sum(case when m='12' then price else 0 end) as December,
sum(case when m='01' then price else 0 end) as January,
sum(case when m='02' then price else 0 end) as February,
sum(case when m='03' then price else 0 end) as June,
sum(price) as price
from
(
SELECT
(
case when
month(t.date) < 4
then
year(t.date)-1
else
year(t.date) end
) as jpfiscal,
month(t.date) as m,
t.price,
t.item
FROM
vhrtransfer as t
where
t.isCredit=0
having jpfiscal=2014
order by year(t.date),m
) as s
group by item;<\/pre>\n\n\n\nDetail of Main \/ Sub queries<\/h2>\n\n\n\n
Sub query 1: pulling data of the target JP Fiscal year from a sorted list<\/h3>\n\n\n\n
pulls data of a target JP fiscal year from a sorted list ; <\/p>\n\n\n\nselect
(
case when
month(datecol) < 4
then
year(datecol)-1
else
year(datecol) end
) as jpfiscal
from
sortedlist
having jpfiscal=2014<\/pre>\n\n\n\ncase when condition<\/strong> then action1<\/strong> else action2<\/strong><\/pre>\n\n\n\n
Sub query 2: pulling data of the target JP fiscal year from an original accounting table.<\/h3>\n\n\n\n
ID<\/td> isCredit
(flag of credit)<\/td>price
(price of item)<\/td>date
(transaction date)<\/td>item
(account item)<\/td><\/tr><\/tbody><\/table>\n\n\n\n FROM
vhrtransfer as t
where
t.isCredit=0
having jpfiscal=2014
order by year(t.date),m
<\/pre>\n\n\n\nMain query: crosstab output of monthly total<\/h3>\n\n\n\n
item<\/th> April
2014<\/th>May
2014<\/th>June<\/strong>
2014<\/strong><\/td>…<\/td> February<\/strong>
2015<\/strong><\/td>March
2015<\/th><\/tr><\/thead><\/table>\n\n\n\nselect item,
\u3000sum in April,
\u3000sum in May,
\u3000\u3000\u3000\uff1a
\u3000sum in March
from
table<\/pre>\n\n\n\n sum(case when m='04' then price else 0 end) as April,<\/pre>\n\n\n\n