select

select KI as 期,
月番号,
to_char(to_date(年 ||'/' ||月, 'yyyy/mm'), 'yyyy/mm/dd') as 年月,
状況,
工事価格,
見積総原価,
売上高,
原価,
粗利益,
当月迄進捗率,
進捗率,
rownum
from
(select KI,
月番号,
DECODE(KI,61,DECODE(Sign(10-月番号),1,'2015','2016'),62,DECODE(Sign(10-月番号),1,'2016','2017'),63,DECODE(Sign(10-月番号),1,'2017','2018'),'2014') as 年,
DECODE(Sign*1 as 月,
DECODE(状況, 1, '-', 2, '◆', 3, '★', 4, '○', 5, ' ', 6, '☆',7, '●',8, '×',' ') as 状況,
工事価格,
見積総原価,
売上高,
原価,
粗利益,
当月迄進捗率,
進捗率,
rownum
from
(select KI,
1 as 月番号,
STATE_1 as 状況,
KOUJI_KAKAKU_1 as 工事価格,
SAISYU_GENKA_1 as 見積総原価,
URIAGE_1 as 売上高,
SIHARAI_1 as 原価,
RIEKI_1 as 粗利益,
SINTYOKU_RUIKEI_1 　as 当月迄進捗率,
SINTYOKU_RUIKEI_1-
(select SINTYOKU_RUIKEI_12
from UD_MONTHLY_SALES M0
where M0.CLOSE_DAY = M1.CLOSE_DAY
and M0.CODE = M1.CODE
and M0.KI =M1.KI-1
) as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES M1

UNION ALL

select KI,
2 as 月番号 ,
STATE_2 as 状況,
KOUJI_KAKAKU_2 as 工事価格,
SAISYU_GENKA_2 as 見積総原価,
URIAGE_2 as 売上高,
SIHARAI_2 as 原価,
RIEKI_2 as 粗利益,
SINTYOKU_RUIKEI_2 as 当月迄進捗率,
SINTYOKU_RUIKEI_2-SINTYOKU_RUIKEI_1 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
3 as 月番号 ,
STATE_3 as 状況,
KOUJI_KAKAKU_3 as 工事価格,
SAISYU_GENKA_3 as 見積総原価,
URIAGE_3 as 売上高,
SIHARAI_3 as 原価,
RIEKI_3 as 粗利益,
SINTYOKU_RUIKEI_3 as 当月迄進捗率,
SINTYOKU_RUIKEI_3-SINTYOKU_RUIKEI_2 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
4 as 月番号 ,
STATE_4 as 状況,
KOUJI_KAKAKU_4 as 工事価格,
SAISYU_GENKA_4 as 見積総原価,
URIAGE_4 as 売上高,
SIHARAI_4 as 原価,
RIEKI_4 as 粗利益,
SINTYOKU_RUIKEI_4 as 当月迄進捗率,
SINTYOKU_RUIKEI_4-SINTYOKU_RUIKEI_3 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
5 as 月番号 ,
STATE_5 as 状況,
KOUJI_KAKAKU_5 as 工事価格,
SAISYU_GENKA_5 as 見積総原価,
URIAGE_5 as 売上高,
SIHARAI_5 as 原価,
RIEKI_5 as 粗利益,
SINTYOKU_RUIKEI_5 as 当月迄進捗率,
SINTYOKU_RUIKEI_5-SINTYOKU_RUIKEI_4 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
6 as 月番号 ,
STATE_6 as 状況,
KOUJI_KAKAKU_6 as 工事価格,
SAISYU_GENKA_6 as 見積総原価,
URIAGE_6 as 売上高,
SIHARAI_6 as 原価,
RIEKI_6 as 粗利益,
SINTYOKU_RUIKEI_6 as 当月迄進捗率,
SINTYOKU_RUIKEI_6-SINTYOKU_RUIKEI_5 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
7 as 月番号 ,
STATE_7 as 状況,
KOUJI_KAKAKU_7 as 工事価格,
SAISYU_GENKA_7 as 見積総原価,
URIAGE_7 as 売上高,
SIHARAI_7 as 原価,
RIEKI_7 as 粗利益,
SINTYOKU_RUIKEI_7 as 当月迄進捗率,
SINTYOKU_RUIKEI_7-SINTYOKU_RUIKEI_6 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
8 as 月番号 ,
STATE_8 as 状況,
KOUJI_KAKAKU_8 as 工事価格,
SAISYU_GENKA_8 as 見積総原価,
URIAGE_8 as 売上高,
SIHARAI_8 as 原価,
RIEKI_8 as 粗利益,
SINTYOKU_RUIKEI_8 as 当月迄進捗率,
SINTYOKU_RUIKEI_8-SINTYOKU_RUIKEI_7 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
9 as 月番号 ,
STATE_9 as 状況,
KOUJI_KAKAKU_9 as 工事価格,
SAISYU_GENKA_9 as 見積総原価,
URIAGE_9 as 売上高,
SIHARAI_9 as 原価,
RIEKI_9 as 粗利益,
SINTYOKU_RUIKEI_9 as 当月迄進捗率,
SINTYOKU_RUIKEI_9-SINTYOKU_RUIKEI_8 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
10 as 月番号 ,
STATE_10 as 状況,
KOUJI_KAKAKU_10 as 工事価格,
SAISYU_GENKA_10 as 見積総原価,
URIAGE_10 as 売上高,
SIHARAI_10 as 原価,
RIEKI_10 as 粗利益,
SINTYOKU_RUIKEI_10 as 当月迄進捗率,
SINTYOKU_RUIKEI_10-SINTYOKU_RUIKEI_9 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
11 as 月番号 ,
STATE_11 as 状況,
KOUJI_KAKAKU_11 as 工事価格,
SAISYU_GENKA_11 as 見積総原価,
URIAGE_11 as 売上高,
SIHARAI_11 as 原価,
RIEKI_11 as 粗利益,
SINTYOKU_RUIKEI_11 as 当月迄進捗率,
SINTYOKU_RUIKEI_11-SINTYOKU_RUIKEI_10 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES

UNION ALL

select KI,
12 as 月番号 ,
STATE_12 as 状況,
KOUJI_KAKAKU_12 as 工事価格,
SAISYU_GENKA_12 as 見積総原価,
URIAGE_12 as 売上高,
SIHARAI_12 as 原価,
RIEKI_12 as 粗利益,
SINTYOKU_RUIKEI_12 as 当月迄進捗率,
SINTYOKU_RUIKEI_12-SINTYOKU_RUIKEI_11 as 進捗率 ,
CODE,
CLOSE_DAY
from UD_MONTHLY_SALES
)
where CLOSE_DAY = '99999999'
and CODE = '622111111'
and 原価 >0
order by KI, 月番号
)

*1: 月番号 +3)-12),1,(月番号-9),0,(月番号+3),-1,(月番号+3