USE <!--MG_schemaname-->;
GO
<!--MGREPEAT-->
IF OBJECT_ID ('<!--MG_filename-->', 'U') IS NOT NULL DROP TABLE <!--MG_filename-->;
GO
CREATE TABLE <!--MG_filename-->
(
VPERIOD varchar(8) COLLATE Japanese_BIN NOT NULL
, VKEY1 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY2 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY3 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY4 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY5 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY6 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY7 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY8 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY9 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY10 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY11 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY12 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY13 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY14 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY15 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY16 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY17 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY18 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY19 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY20 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY21 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY22 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY23 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY24 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY25 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY26 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY27 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY28 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY29 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY30 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY31 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY32 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY33 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY34 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY35 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY36 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY37 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY38 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY39 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY40 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY41 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY42 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY43 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY44 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY45 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY46 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY47 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY48 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY49 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY50 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY51 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY52 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY53 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY54 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY55 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY56 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY57 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY58 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY59 varchar(60) COLLATE Japanese_BIN NOT NULL
, VKEY60 varchar(60) COLLATE Japanese_BIN NOT NULL
, VD1 numeric(14, 2) NOT NULL
, VD2 numeric(14, 2) NOT NULL
, VD3 numeric(14, 2) NOT NULL
, VD4 numeric(14, 2) NOT NULL
, VD5 numeric(14, 2) NOT NULL
, VD6 numeric(14, 2) NOT NULL
, VD7 numeric(14, 2) NOT NULL
, VD8 numeric(14, 2) NOT NULL
, VD9 numeric(14, 2) NOT NULL
, VD10 numeric(14, 2) NOT NULL
, VD11 numeric(14, 2) NOT NULL
, VD12 numeric(14, 2) NOT NULL
, VD13 numeric(14, 2) NOT NULL
, VD14 numeric(14, 2) NOT NULL
, VD15 numeric(14, 2) NOT NULL
, VD16 numeric(14, 2) NOT NULL
, VD17 numeric(14, 2) NOT NULL
, VD18 numeric(14, 2) NOT NULL
, VD19 numeric(14, 2) NOT NULL
, VD20 numeric(14, 2) NOT NULL
, VD21 numeric(14, 2) NOT NULL
, VD22 numeric(14, 2) NOT NULL
, VD23 numeric(14, 2) NOT NULL
, VD24 numeric(14, 2) NOT NULL
, VD25 numeric(14, 2) NOT NULL
, VD26 numeric(14, 2) NOT NULL
, VD27 numeric(14, 2) NOT NULL
, VD28 numeric(14, 2) NOT NULL
, VD29 numeric(14, 2) NOT NULL
, VD30 numeric(14, 2) NOT NULL
, VD31 numeric(14, 2) NOT NULL
, VD32 numeric(14, 2) NOT NULL
, VD33 numeric(14, 2) NOT NULL
, VD34 numeric(14, 2) NOT NULL
, VD35 numeric(14, 2) NOT NULL
, VD36 numeric(14, 2) NOT NULL
, VD37 numeric(14, 2) NOT NULL
, VD38 numeric(14, 2) NOT NULL
, VD39 numeric(14, 2) NOT NULL
, VD40 numeric(14, 2) NOT NULL
, VD41 numeric(14, 2) NOT NULL
, VD42 numeric(14, 2) NOT NULL
, VD43 numeric(14, 2) NOT NULL
, VD44 numeric(14, 2) NOT NULL
, VD45 numeric(14, 2) NOT NULL
, VD46 numeric(14, 2) NOT NULL
, VD47 numeric(14, 2) NOT NULL
, VD48 numeric(14, 2) NOT NULL
, VD49 numeric(14, 2) NOT NULL
, VD50 numeric(14, 2) NOT NULL
, VD51 numeric(14, 2) NOT NULL
, VD52 numeric(14, 2) NOT NULL
, VD53 numeric(14, 2) NOT NULL
, VD54 numeric(14, 2) NOT NULL
, VD55 numeric(14, 2) NOT NULL
, VD56 numeric(14, 2) NOT NULL
, VD57 numeric(14, 2) NOT NULL
, VD58 numeric(14, 2) NOT NULL
, VD59 numeric(14, 2) NOT NULL
, VD60 numeric(14, 2) NOT NULL
, VKEYP int IDENTITY(1,1) NOT NULL
)
GO
ALTER TABLE <!--MG_filename--> ADD PRIMARY KEY (VPERIOD<!--MG_pkey-->,VKEYP);
GO
INSERT INTO <!--MG_filename-->
SELECT T0.* FROM (SELECT
B.<!--MG_period--> "VPERIOD"
,A.KEY1 "VKEY1"
,A.KEY2 "VKEY2"
,A.KEY3 "VKEY3"
,A.KEY4 "VKEY4"
,A.KEY5 "VKEY5"
,A.KEY6 "VKEY6"
,A.KEY7 "VKEY7"
,A.KEY8 "VKEY8"
,CASE A.KEY3 WHEN ' ' THEN A.KEY9 ELSE ISNULL(C.EX_VALUE,' ') END "VKEY9"
,CASE A.KEY3 WHEN ' ' THEN A.KEY10 ELSE ISNULL(D.EX_VALUE,' ') END "VKEY10"
,CASE A.KEY3 WHEN ' ' THEN A.KEY11 ELSE ISNULL(E.EX_VALUE,' ') END "VKEY11"
,CASE A.KEY8 WHEN ' ' THEN A.KEY12 ELSE ISNULL(F.EX_VALUE,' ') END "VKEY12"
,CASE A.KEY8 WHEN ' ' THEN A.KEY13 ELSE ISNULL(G.EX_VALUE,' ') END "VKEY13"
,CASE A.KEY8 WHEN ' ' THEN A.KEY14 ELSE ISNULL(H.EX_VALUE,' ') END "VKEY14"
,CASE A.KEY1 WHEN ' ' THEN A.KEY15 ELSE ISNULL(I.EX_VALUE,' ') END "VKEY15"
,CASE A.KEY1 WHEN ' ' THEN A.KEY16 ELSE ISNULL(J.EX_VALUE,' ') END "VKEY16"
,A.KEY17 "VKEY17"
,A.KEY18 "VKEY18"
,A.KEY19 "VKEY19"
,A.KEY20 "VKEY20"
,A.KEY21 "VKEY21"
,A.KEY22 "VKEY22"
,A.KEY23 "VKEY23"
,A.KEY24 "VKEY24"
,A.KEY25 "VKEY25"
,A.KEY26 "VKEY26"
,A.KEY27 "VKEY27"
,A.KEY28 "VKEY28"
,A.KEY29 "VKEY29"
,A.KEY30 "VKEY30"
,A.KEY31 "VKEY31"
,A.KEY32 "VKEY32"
,A.KEY33 "VKEY33"
,A.KEY34 "VKEY34"
,A.KEY35 "VKEY35"
,A.KEY36 "VKEY36"
,A.KEY37 "VKEY37"
,A.KEY38 "VKEY38"
,A.KEY39 "VKEY39"
,A.KEY40 "VKEY40"
,A.KEY41 "VKEY41"
,A.KEY42 "VKEY42"
,A.KEY43 "VKEY43"
,A.KEY44 "VKEY44"
,A.KEY45 "VKEY45"
,A.KEY46 "VKEY46"
,A.KEY47 "VKEY47"
,A.KEY48 "VKEY48"
,A.KEY49 "VKEY49"
,A.KEY50 "VKEY50"
,A.KEY51 "VKEY51"
,A.KEY52 "VKEY52"
,A.KEY53 "VKEY53"
,A.KEY54 "VKEY54"
,A.KEY55 "VKEY55"
,A.KEY56 "VKEY56"
,A.KEY57 "VKEY57"
,A.KEY58 "VKEY58"
,A.KEY59 "VKEY59"
,A.KEY60 "VKEY60"
,SUM(A.D1) "VD1"
,SUM(A.D2) "VD2"
,SUM(A.D3) "VD3"
,SUM(A.D4) "VD4"
,SUM(A.D5) "VD5"
,SUM(A.D6) "VD6"
,SUM(A.D7) "VD7"
,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9"
,SUM(A.D10) "VD10"
,SUM(A.D11) "VD11"
,SUM(A.D12) "VD12"
,SUM(A.D13) "VD13"
,SUM(A.D14) "VD14"
,SUM(A.D15) "VD15"
,SUM(A.D16) "VD16"
,SUM(A.D17) "VD17"
,SUM(A.D18) "VD18"
,SUM(A.D19) "VD19"
,SUM(A.D20) "VD20"
,SUM(A.D21) "VD21"
,SUM(A.D22) "VD22"
,SUM(A.D23) "VD23"
,SUM(A.D24) "VD24"
,SUM(A.D25) "VD25"
,SUM(A.D26) "VD26"
,SUM(A.D27) "VD27"
,SUM(A.D28) "VD28"
,SUM(A.D29) "VD29"
,SUM(A.D30) "VD30"
,SUM(A.D31) "VD31"
,SUM(A.D32) "VD32"
,SUM(A.D33) "VD33"
,SUM(A.D34) "VD34"
,SUM(A.D35) "VD35"
,SUM(A.D36) "VD36"
,SUM(A.D37) "VD37"
,SUM(A.D38) "VD38"
,SUM(A.D39) "VD39"
,SUM(A.D40) "VD40"
,SUM(A.D41) "VD41"
,SUM(A.D42) "VD42"
,SUM(A.D43) "VD43"
,SUM(A.D44) "VD44"
,SUM(A.D45) "VD45"
,SUM(A.D46) "VD46"
,SUM(A.D47) "VD47"
,SUM(A.D48) "VD48"
,SUM(A.D49) "VD49"
,SUM(A.D50) "VD50"
,SUM(A.D51) "VD51"
,SUM(A.D52) "VD52"
,SUM(A.D53) "VD53"
,SUM(A.D54) "VD54"
,SUM(A.D55) "VD55"
,SUM(A.D56) "VD56"
,SUM(A.D57) "VD57"
,SUM(A.D58) "VD58"
,SUM(A.D59) "VD59"
,SUM(A.D60) "VD60"
FROM <!--MG_from--> A
INNER JOIN EX_DAY B ON B.EX_DATE = A.<!--MG_dateid-->
LEFT OUTER JOIN EX_HENKAN C ON C.EX_KUBUN = '部課' AND C.EX_KEY = A.KEY3
LEFT OUTER JOIN EX_HENKAN D ON D.EX_KUBUN = '担当' AND D.EX_KEY = A.KEY3
LEFT OUTER JOIN EX_HENKAN E ON E.EX_KUBUN = '業種' AND E.EX_KEY = A.KEY3
LEFT OUTER JOIN EX_HENKAN F ON F.EX_KUBUN = '大分類' AND F.EX_KEY = A.KEY8
LEFT OUTER JOIN EX_HENKAN G ON G.EX_KUBUN = 'カテゴリ' AND G.EX_KEY = A.KEY8
LEFT OUTER JOIN EX_HENKAN H ON H.EX_KUBUN = 'メーカ' AND H.EX_KEY = A.KEY8
LEFT OUTER JOIN EX_HENKAN I ON I.EX_KUBUN = '実績部課' AND I.EX_KEY = A.KEY1
LEFT OUTER JOIN EX_HENKAN J ON J.EX_KUBUN = '実績部' AND J.EX_KEY = A.KEY1
GROUP BY
B.<!--MG_period-->
,A.KEY1
,A.KEY2
,A.KEY3
,A.KEY4
,A.KEY5
,A.KEY6
,A.KEY7
,A.KEY8
,CASE A.KEY3 WHEN ' ' THEN A.KEY9 ELSE ISNULL(C.EX_VALUE,' ') END
,CASE A.KEY3 WHEN ' ' THEN A.KEY10 ELSE ISNULL(D.EX_VALUE,' ') END
,CASE A.KEY3 WHEN ' ' THEN A.KEY11 ELSE ISNULL(E.EX_VALUE,' ') END
,CASE A.KEY8 WHEN ' ' THEN A.KEY12 ELSE ISNULL(F.EX_VALUE,' ') END
,CASE A.KEY8 WHEN ' ' THEN A.KEY13 ELSE ISNULL(G.EX_VALUE,' ') END
,CASE A.KEY8 WHEN ' ' THEN A.KEY14 ELSE ISNULL(H.EX_VALUE,' ') END
,CASE A.KEY1 WHEN ' ' THEN A.KEY15 ELSE ISNULL(I.EX_VALUE,' ') END
,CASE A.KEY1 WHEN ' ' THEN A.KEY16 ELSE ISNULL(J.EX_VALUE,' ') END
,A.KEY17
,A.KEY18
,A.KEY19
,A.KEY20
,A.KEY21
,A.KEY22
,A.KEY23
,A.KEY24
,A.KEY25
,A.KEY26
,A.KEY27
,A.KEY28
,A.KEY29
,A.KEY30
,A.KEY31
,A.KEY32
,A.KEY33
,A.KEY34
,A.KEY35
,A.KEY36
,A.KEY37
,A.KEY38
,A.KEY39
,A.KEY40
,A.KEY41
,A.KEY42
,A.KEY43
,A.KEY44
,A.KEY45
,A.KEY46
,A.KEY47
,A.KEY48
,A.KEY49
,A.KEY50
,A.KEY51
,A.KEY52
,A.KEY53
,A.KEY54
,A.KEY55
,A.KEY56
,A.KEY57
,A.KEY58
,A.KEY59
,A.KEY60
) T0;
GO

<!--MGREPEAT-->

CREATE INDEX IDX_<!--MG_filename2-->_<!--MG_keyno-->
ON <!--MG_filename2-->
(
VPERIOD
,VKEY<!--MG_keyno-->
)
;
GO
<!--MGENDREPEAT-->

<!--MGENDREPEAT-->