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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
/****** 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; |
