[T-SQL] Pivot語法

2010-12-13

摘要:[T-SQL] Pivot語法

遇到一些撈資料的問題, 百敬老師丟了一個語法使用到Pivot,拿來跟原來寫的作了個比較

下方為從sys.dm_os_performance_counters 的DMV 查詢'Full Scans/sec' 與 'Index Searches/sec'

select sum(case when lower(p.counter_name) = 'Full Scans/sec' then convert(numeric,cntr_value) else 0 end) as FullScans
,sum(case when lower(p.counter_name) = 'Index Searches/sec' then convert(numeric,cntr_value) else 0 end) as IndexSearches
,sum(case when lower(p.counter_name) = 'Workfiles Created/sec' then convert(numeric,cntr_value) else 0 end) as WorkfilesCreated
,sum(case when lower(p.counter_name) = 'Worktables Created/sec' then convert(numeric,cntr_value) else 0 end) as WorktablesCreated
         from sys.dm_os_performance_counters as p with (readpast)
         where p.object_name = (select case when convert(sysname,serverproperty('ServerName')) like '%\%' then 'MSSQL$' + @@servicename else 'SQLServer' end)+ ':Access Methods'
         and  p.instance_name <> '_Total'
         group by p.instance_name
         order by 1

其實是把所有SQL Server :Access Methods的counter各別撈出來作Sum()

執行計畫如下圖：

使用Pivot語法類似樞紐分析表的功能，讓可由某個資料行多筆記錄轉成多欄，並在欄列交錯的資料填入彙總值

Select < Pivot 運算後形成的暫存資料表資料行列表> From <來源資料表>

Pivot (彙總函數(提供彙總值的資料行)

For Pivot 資料行 IN (資料行列表)

) 資料表的別名

Where 條件運算

下列查詢原來是一筆筆出現，但希望他轉置呈現

select object_name,counter_name,cntr_value from sys.dm_os_performance_counters
where counter_name in('Full Scans/sec','Index Searches/sec', 'Workfiles Created/sec','Worktables Created/sec' )

再上述語法外，把Pivot方法加上去，即可轉置

select * from (
select object_name,counter_name,cntr_value from sys.dm_os_performance_counters
where counter_name in('Full Scans/sec','Index Searches/sec', 'Workfiles Created/sec','Worktables Created/sec' ) ) tPivot

pivot(sum(cntr_value) for Counter_name in ([Full Scans/sec],[Index Searches/sec], [Workfiles Created/sec],[Worktables Created/sec])) as t

就執行計畫來看，這兩種查詢成本不會差很多，但比較兩種寫法Pivot可以省一點code，可以參考看看

回首頁