How to pivot a result set

Question

I've been tasked to monitor the performance of SQL AlwaysON. I've written a script that would return AlwaysON Availability group information as follow:

;
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

Here is the image of the output: https://i.sstatic.net/jLcMt.png

How can "pivot" the result to 1 row that would look something like this:

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)

I am looking for a way that works like 'chart' command in Splunk where it spans the columns against multiple values by secondary_replica

Answer 1

This replaces your main query. CTEs stay the same.

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;

Useful Sources:

SQL Server Pivot Table with multiple column aggregates

MSDN