|
아래 counterdata 테이블과 counterdetails을 pivot문을 사용하여 pivot테이블을 만들고 싶습니다.
그런데 피벗할 컬럼이 counterdetail의 countername만 하는것이 아니고 일부 instancename(Disk부분)도 pivot을 하려니 잘 안되네요....
for문 같은 경우 한개의 컬럼 지정만 가능한건지요?? 맨 아래 쿼리문은 디스크 부분은 빼고 작성한것입니다.
보시고 답변 부탁드립니다.
counterdata
ID
|
Data
|
1
|
188
|
2
|
199
|
3
|
196
|
4
|
207
|
5
|
211
|
counterdetails
ID
|
Machinename
|
Objectname
|
Countername
|
Instancename
|
1
|
A
|
Memory
|
Available MBytes
|
NULL
|
2
|
B
|
Memory
|
Free System Page Table Entries
|
NULL
|
3
|
C
|
Memory
|
Pool Nonpaged Bytes
|
NULL
|
4
|
D
|
Memory
|
Pool Paged Bytes
|
NULL
|
5
|
E
|
Processor
|
% Processor Time
|
_Total
|
6
|
F
|
LogicalDisk
|
% Free Space
|
C:
|
7
|
G
|
LogicalDisk
|
% Free Space
|
D:
|
pivot
Machinename
|
Process_Total
|
Memory_Used
|
PTE
|
PNP_Bytes
|
PP_Bytes
|
C_Drive
|
D_Drive
|
A
|
10
|
3,248
|
175,613
|
48,323,429
|
84,425,822
|
50
|
43
|
B
|
6
|
288
|
187,261
|
121,319,714
|
140,900,076
|
60
|
69
|
C
|
2
|
2,588
|
177,180
|
44,580,612
|
88,802,190
|
70
|
85
|
D
|
2
|
3,377
|
177,810
|
58,580,703
|
161,709,910
|
60
|
45
|
E
|
6
|
1,149
|
186,946
|
25,859,616
|
103,455,245
|
55
|
25
|
select machinename, "% Processor Time" as Process_Total,
"Available MBytes" as Memory_Used, "Free System Page Table Entries" as PTE,
"Pool Nonpaged Bytes" as PNP_Bytes, "Pool Paged Bytes" as PP_Bytes
from
(select machinename,countername,countervalue
from counterdetails as a join counterdata as b
on a.counterid = b.counterid
) pvt
pivot
(
avg(countervalue)
FOR countername IN
( [% Processor Time], [Available MBytes], [Free System Page Table Entries],
[Pool Nonpaged Bytes], [Pool Paged Bytes] )
) AS pvt
ORDER BY machinename
|
