如何透视结果集

Question

我的任务是监视 SQL AlwaysON 的性能。我编写了一个脚本，该脚本将返回 AlwaysON 可用性组信息，如下所示：

;
WITH basicaginfo AS(
SELECT 

ag.name AS AvailabilityGroupName,
cs.replica_server_name AS NodeName,
rs.role_desc,
rs.synchronization_health_desc,
DB_NAME(drs.database_id) AS DatabaseName

FROM 
sys.availability_groups ag
join
sys.dm_hadr_availability_replica_cluster_states cs on ag.group_id = cs.group_id
join
sys.dm_hadr_availability_replica_states rs ON (ag.group_id=rs.group_id AND cs.replica_id = rs.replica_id)
join
sys.dm_hadr_database_replica_states drs ON (ag.group_id=drs.group_id AND cs.replica_id = drs.replica_id)

WHERE
cs.replica_server_name = @@SERVERNAME
),

AG_Stats AS 
(
SELECT AR.replica_server_name,
HARS.role_desc, 
Db_name(DRS.database_id) [DBName], 
DRS.last_commit_time
FROM   sys.dm_hadr_database_replica_states DRS 
INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id 
INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id 
AND AR.replica_id = HARS.replica_id 
),
Pri_CommitTime AS 
(
SELECT replica_server_name
, DBName
, last_commit_time
FROM   AG_Stats
WHERE  role_desc = 'PRIMARY'
),
Sec_CommitTime AS 
(
SELECT replica_server_name
, DBName
, last_commit_time
FROM   AG_Stats
WHERE  role_desc = 'SECONDARY'
)
SELECT 
		bb.*,
		p.replica_server_name [primary_replica]
, s.replica_server_name [secondary_replica]
, DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Lag_Secs]

FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]
left join basicaginfo bb on p.replica_server_name=bb.NodeName and p.DBName=bb.DatabaseName

下面是输出的图像： https://i.stack.imgur.com/jLcMt.png

如何将结果"透视"到 1 行，如下所示：

AvailabilityGroupName  Primary      LAB-SCB-SQL02   LAB-LAS-SQL01  LAB-LAS-SQL02 Sync_Lag_Secs
LAB-VIP-USADB      LAB-SCB-SQL01   HEALTHY	        HEALTHY	      HEALTHY	      max(3)

我正在寻找一种类似于 Splunk 中的"chart"命令的方法，它通过secondary_replica跨越多个值的列

Answer 1

这将替换您的主查询。CTE 保持不变。

SELECT AvailabilityGroupName, NodeName, role_desc, [primary_replica],
-- Replace Server1, 2, 3 etc with your specific values. Keep square brackets
[Server1], [Server2], [Server3], [Max_Sync_Lag_Secs]
FROM(
SELECT 
bb.AvailabilityGroupName, bb.NodeName, bb.role_desc, bb.synchronization_health_desc, bb.DatabaseName,
p.replica_server_name AS [primary_replica],
s.replica_server_name AS [secondary_replica],
DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Lag_Secs],
MAX( DATEDIFF(ss,s.last_commit_time,p.last_commit_time)) OVER( PARTITION BY NULL ) AS [Max_Sync_Lag_Secs]
FROM Pri_CommitTime AS p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]
LEFT JOIN basicaginfo bb on p.replica_server_name=bb.NodeName and p.DBName=bb.DatabaseName 
) AS Data
PIVOT(
-- Replace Server1, 2, 3 etc with your specific values. Keep square brackets
MAX( synchronization_health_desc ) FOR [secondary_replica] IN( [Server1], [Server2], [Server3] )) AS PivotedData;

有用的来源：

具有多个列聚合的 SQL Server 数据透视表

MSDN