-
Notifications
You must be signed in to change notification settings - Fork 0
/
Oralce PIVOT行转列
139 lines (131 loc) · 4.69 KB
/
Oralce PIVOT行转列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
DEMO1 ：
WITH TEMP AS
(SELECT A.MACHINENAME, A.TRANSFERSTATE, COUNT(*) AS STATE_NUM
FROM PORT A
WHERE A.TRANSFERSTATE IN ('ReadyToUnload',
'ReadyToLoad',
'ReservedToUnload',
'ReservedToLoad')
AND A.FACTORYNAME = 'LBP'
GROUP BY A.MACHINENAME, A.TRANSFERSTATE),
A AS
(SELECT A.MACHINENAME, COUNT(A.PORTNAME) AS PORT_NUM
FROM PORT A
WHERE A.FACTORYNAME = 'LBP'
GROUP BY A.MACHINENAME),
B AS
(SELECT *
FROM (SELECT MACHINENAME, TRANSFERSTATE, STATE_NUM FROM TEMP)
PIVOT(SUM(STATE_NUM)
FOR TRANSFERSTATE IN('ReadyToUnload' AS READYTOUNLOAD,
'ReadyToLoad' AS READYTOLOAD,
'ReservedToUnload' AS RESERVEDTOUNLOAD,
'ReservedToLoad' AS RESERVEDTOLOAD)))
SELECT T.MACHINENAME,
T.PORT_NUM,
T1.READYTOUNLOAD,
T1.READYTOLOAD,
T1.RESERVEDTOUNLOAD,
T1.RESERVEDTOLOAD
FROM A T
INNER JOIN B T1
ON (T.MACHINENAME = T1.MACHINENAME)
DEMO2 ：
WITH STD_INFO AS
(SELECT T1.OPERATIONSEQ,
T1.OPERATIONDESC,
T.MACHINENAME,
T.HOUR_SHIFT,
SUM(T.LOT_QTY) AS LOT_QTY
FROM DWD_MMTSPEC_N_MACHINE T1
LEFT JOIN DWD_MOVEMENT_1H_MACHINE T
ON (T.MACHINENAME = T1.MACHINENAME)
WHERE T.PRODUCTIONTYPE = 'Production'
AND T.PRODUCTFACTORYTYPE = 'LBP'
AND T.REPORTDATE = '20200821'
GROUP BY T1.OPERATIONSEQ, T1.OPERATIONDESC, T.MACHINENAME, T.HOUR_SHIFT
ORDER BY T1.OPERATIONSEQ, T.HOUR_SHIFT),
SUM_INFO AS
(SELECT T1.OPERATIONSEQ,
T1.OPERATIONDESC,
SUBSTR(T.MACHINENAME, 1, 5) || '_SUM' AS MACHINENAME,
T.HOUR_SHIFT,
SUM(T.LOT_QTY) / MAX(T1.DENOMINATOR) AS AVG_NUM
FROM DWD_MMTSPEC_N_MACHINE T1
LEFT JOIN DWD_MOVEMENT_1H_MACHINE T
ON (T.MACHINENAME = T1.MACHINENAME)
WHERE T.PRODUCTIONTYPE = 'Production'
AND T.PRODUCTFACTORYTYPE = 'LBP'
AND T.REPORTDATE = '20200821'
GROUP BY T1.OPERATIONSEQ,
T1.OPERATIONDESC,
SUBSTR(T.MACHINENAME, 1, 5) || '_SUM',
T.HOUR_SHIFT
ORDER BY T1.OPERATIONSEQ, T.HOUR_SHIFT)
SELECT *
FROM (SELECT *
FROM (SELECT OPERATIONSEQ,
OPERATIONDESC,
MACHINENAME,
HOUR_SHIFT,
LOT_QTY
FROM STD_INFO)
PIVOT(SUM(LOT_QTY)
FOR HOUR_SHIFT IN('06' AS "06",
'07' AS "07",
'08' AS "08",
'09' AS "09",
'10' AS "10",
'11' AS "11",
'12' AS "12",
'13' AS "13",
'14' AS "14",
'15' AS "15",
'16' AS "16",
'17' AS "17",
'18' AS "18",
'19' AS "19",
'20' AS "20",
'21' AS "21",
'22' AS "22",
'23' AS "23",
'00' AS "00",
'01' AS "01",
'02' AS "02",
'03' AS "03",
'04' AS "04",
'05' AS "05"))
UNION ALL
SELECT *
FROM (SELECT OPERATIONSEQ,
OPERATIONDESC,
MACHINENAME,
HOUR_SHIFT,
AVG_NUM
FROM SUM_INFO)
PIVOT(SUM(AVG_NUM)
FOR HOUR_SHIFT IN('06' AS "06",
'07' AS "07",
'08' AS "08",
'09' AS "09",
'10' AS "10",
'11' AS "11",
'12' AS "12",
'13' AS "13",
'14' AS "14",
'15' AS "15",
'16' AS "16",
'17' AS "17",
'18' AS "18",
'19' AS "19",
'20' AS "20",
'21' AS "21",
'22' AS "22",
'23' AS "23",
'00' AS "00",
'01' AS "01",
'02' AS "02",
'03' AS "03",
'04' AS "04",
'05' AS "05")))
ORDER BY OPERATIONSEQ, OPERATIONDESC, MACHINENAME;