Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



T-SQL Transform Row Data into Columns using PIVOT
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-02-02









Within a Microsoft SQL Transactional Replication setup there is a method to capture Commands in Distribution. These are commands in the Distribution DB that are marked as pending transmission to the designated Subscribers. It is possible to log this data and then report on it using an Aggregated Pivot Query. The query example is below using raw data and pivot data.



Example:


Raw Data:

Select LogID, Subscriber, PendingCMDCount, InsertedDate from dbo.LOG_REPLICATION_UNDISTRIBUTED_COMMANDS





T-SQL Solution: Pivot Data

Select
[Date-Year], [Date-Month], [Date-Day],
[Server-1], [Server-2], [Server-3], [Server-4],
[Server-5], [Server-6], [Server-7], [Server-8]
From
(Select
DATEPART(YY,InsertedDate) as 'Date-Year', DATEPART(M,InsertedDate) as 'Date-Month', DATEPART(D,InsertedDate) as 'Date-Day',
Subscriber, SUM(PendingCMDCount) as 'PendingCMDCount' from
dbo.LOG_REPLICATION_UNDISTRIBUTED_COMMANDS t1
Group By DATEPART(YY,t1.InsertedDate), DATEPART(M,t1.InsertedDate), DATEPART(D,t1.InsertedDate), Subscriber) t2
Pivot
(SUM(PendingCMDCount)
FOR t2.[Subscriber] IN ([Server-1], [Server-2], [Server-3], [Server-4],
[Server-5], [Server-6], [Server-7], [Server-8])
) AS pvt
ORDER BY [Date-Year], [Date-Month], [Date-Day]