| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

T-SQL Transform Row Data into Columns using PIVOT
by BF (Principal Consultant; Architecture; Engineering)

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.


Raw Data:

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

T-SQL Solution: Pivot Data

[Date-Year], [Date-Month], [Date-Day],
[Server-1], [Server-2], [Server-3], [Server-4],
[Server-5], [Server-6], [Server-7], [Server-8]
DATEPART(YY,InsertedDate) as 'Date-Year', DATEPART(M,InsertedDate) as 'Date-Month', DATEPART(D,InsertedDate) as 'Date-Day',
Subscriber, SUM(PendingCMDCount) as 'PendingCMDCount' from
Group By DATEPART(YY,t1.InsertedDate), DATEPART(M,t1.InsertedDate), DATEPART(D,t1.InsertedDate), Subscriber) t2
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]