Pivoting a dynamic set

Tweet
Share
Share
0 Shares

SQL Server does not allow for a dynamic set, which can be problematic if you need to pivot based on the data that comes from rows and you can have various values in the column.

In general, you need a stored procedure and dynamic SQL in order to handle the dynamic pivots. This requires uses of metadata such as sys.columns table. Thus in general the steps needs to be:

  • Define the columns that needs to be present in the SELECT
  • Define the columns that will create the new pivoted columns, usually a SELECT over the original column containing the data that needs to be pivoted.
  • Assemble the dynamic SQL
  • Execute the dynamic SQL, returning the result set.
/****** Object:  StoredProcedure [dbo].[uspResearchByEventsReporting] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspResearchByEventsReporting] AS
BEGIN
	DECLARE
		@PivotColumnList nvarchar(MAX),
		@AliasColumnList nvarchar(MAX),
		@PivotSql nvarchar(MAX)
	;

	SET @PivotColumnList = (STUFF((
		SELECT CONCAT(N',', QUOTENAME(m.EventResearchMetricID))
		FROM dbo.tblResearchByEventMetrics AS m
		ORDER BY m.EventResearchMetricID
		FOR XML PATH(N'')
	),1,1,N''));

	SET @AliasColumnList = (STUFF((
		SELECT CONCAT(N',p.', QUOTENAME(m.EventResearchMetricID), N' AS ', QUOTENAME(m.MetricCaption))
		FROM dbo.tblResearchByEventMetrics AS m
		ORDER BY m.EventResearchMetricID
		FOR XML PATH(N'')
	),1,1,N''));

	SET @PivotSql = CONCAT(
	N'WITH BaseData AS (
		SELECT 
			e.EventID,
			eg.EventResearchMetricGroupID,
			em.EventResearchMetricID,
			e.EventName,
			e.EventStartDate,
			e.EventStartTime,
			eg.EntryDate AS GroupEntryDate,
			re.Measurement
		FROM dbo.tblResearchByEvent AS re
		INNER JOIN dbo.tblResearchByEventMetrics AS em
			ON re.MetricID = em.EventResearchMetricID
		INNER JOIN dbo.tblResearchByEventMetricGroups AS eg
			ON re.GroupID = eg.EventResearchMetricGroupID
		INNER JOIN dbo.tblEvents AS e
			ON eg.EventID = e.EventID
	) 
	SELECT
		p.EventID,
		p.EventResearchMetricGroupID,
		p.EventName,
		p.EventStartDate,
		p.EventStartTime,
		p.GroupEntryDate,
		', @AliasColumnList, N'
	FROM BaseData AS d
	PIVOT (
		MAX(d.Measurement)
		FOR d.EventResearchMetricID
		IN (', @PivotColumnList, N')
	) AS p;');

	EXEC sys.sp_executesql @PivotSql;
END;

Related Posts