Können Sie Zwischensumme Zeilen und / oder Spalten in einer Pivot-Tabelle?

Ich habe eine Reihe von Abfragen, die eine Pivot-Tabelle ausgibt. Ist es möglich, Zeilen- und / oder Spalten-Zwischensummen für eine Pivot-Tabelle zu erhalten?

Mein Tisch, den ich aussehe, sieht so aus

Site FormID Present Site 1 Form A Yes Site 1 Form B Yes Site 1 Form D Yes 

etc…

Meine Pivot-Tabellenabfrage ist das

  SELECT * FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID FROM @CRFCount WHERE Present='Yes' GROUP BY Site, FormID) d PIVOT (SUM(NumberOfForms) FOR [Site] IN ([Site 1], [Site 2], [Site 3]) ) AS p; 

Aber ich möchte wirklich, dass es dazu kommt (was natürlich nicht für mich gilt)

  FormID Site 1 Site 2 Site 3 Total Form A 8 8 15 31 Form B 14 4 NULL 18 Form C 14 NULL NULL 14 Form D 15 3 16 34 Form E 12 4 NULL 16 Form F 14 5 5 24 Form G 14 8 6 28 Form H 22 10 15 47 Form I 15 10 16 41 Form J 15 5 16 36 Total 143 57 89 289 

Danke für deine Hilfe !

-Don

 ;WITH C as ( SELECT FormID, [Site 1], [Site 2], [Site 3], (SELECT SUM(S) FROM (VALUES([Site 1]), ([Site 2]), ([Site 3])) AS T(S)) as Total FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID FROM @CRFCount WHERE Present='Yes' GROUP BY Site, FormID) d PIVOT (SUM(NumberOfForms) FOR [Site] IN ([Site 1], [Site 2], [Site 3]) ) AS p ) SELECT * FROM ( SELECT FormID, [Site 1], [Site 2], [Site 3], Total FROM C UNION ALL SELECT 'Total', SUM([Site 1]), SUM([Site 2]), SUM([Site 3]), SUM(Total) FROM C ) AS T ORDER BY CASE WHEN FormID = 'Total' THEN 1 END ( ;WITH C as ( SELECT FormID, [Site 1], [Site 2], [Site 3], (SELECT SUM(S) FROM (VALUES([Site 1]), ([Site 2]), ([Site 3])) AS T(S)) as Total FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID FROM @CRFCount WHERE Present='Yes' GROUP BY Site, FormID) d PIVOT (SUM(NumberOfForms) FOR [Site] IN ([Site 1], [Site 2], [Site 3]) ) AS p ) SELECT * FROM ( SELECT FormID, [Site 1], [Site 2], [Site 3], Total FROM C UNION ALL SELECT 'Total', SUM([Site 1]), SUM([Site 2]), SUM([Site 3]), SUM(Total) FROM C ) AS T ORDER BY CASE WHEN FormID = 'Total' THEN 1 END ) AS p ;WITH C as ( SELECT FormID, [Site 1], [Site 2], [Site 3], (SELECT SUM(S) FROM (VALUES([Site 1]), ([Site 2]), ([Site 3])) AS T(S)) as Total FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID FROM @CRFCount WHERE Present='Yes' GROUP BY Site, FormID) d PIVOT (SUM(NumberOfForms) FOR [Site] IN ([Site 1], [Site 2], [Site 3]) ) AS p ) SELECT * FROM ( SELECT FormID, [Site 1], [Site 2], [Site 3], Total FROM C UNION ALL SELECT 'Total', SUM([Site 1]), SUM([Site 2]), SUM([Site 3]), SUM(Total) FROM C ) AS T ORDER BY CASE WHEN FormID = 'Total' THEN 1 END ) ;WITH C as ( SELECT FormID, [Site 1], [Site 2], [Site 3], (SELECT SUM(S) FROM (VALUES([Site 1]), ([Site 2]), ([Site 3])) AS T(S)) as Total FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID FROM @CRFCount WHERE Present='Yes' GROUP BY Site, FormID) d PIVOT (SUM(NumberOfForms) FOR [Site] IN ([Site 1], [Site 2], [Site 3]) ) AS p ) SELECT * FROM ( SELECT FormID, [Site 1], [Site 2], [Site 3], Total FROM C UNION ALL SELECT 'Total', SUM([Site 1]), SUM([Site 2]), SUM([Site 3]), SUM(Total) FROM C ) AS T ORDER BY CASE WHEN FormID = 'Total' THEN 1 END ( ;WITH C as ( SELECT FormID, [Site 1], [Site 2], [Site 3], (SELECT SUM(S) FROM (VALUES([Site 1]), ([Site 2]), ([Site 3])) AS T(S)) as Total FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID FROM @CRFCount WHERE Present='Yes' GROUP BY Site, FormID) d PIVOT (SUM(NumberOfForms) FOR [Site] IN ([Site 1], [Site 2], [Site 3]) ) AS p ) SELECT * FROM ( SELECT FormID, [Site 1], [Site 2], [Site 3], Total FROM C UNION ALL SELECT 'Total', SUM([Site 1]), SUM([Site 2]), SUM([Site 3]), SUM(Total) FROM C ) AS T ORDER BY CASE WHEN FormID = 'Total' THEN 1 END 

Hinweis: Wenn Sie SQL server 2005 verwenden, müssen Sie dies ändern:

  (SELECT SUM(S) FROM (VALUES([Site 1]), ([Site 2]), ([Site 3])) AS T(S)) as Total 

nach

  (SELECT SUM(S) FROM (SELECT [Site 1] UNION ALL SELECT [Site 2] UNION ALL SELECT [Site 3]) AS T(S)) as Total 

Versuche auf SE data

Probieren Sie dies aus (nicht getestet):

 SELECT * FROM ( SELECT Site = case when grouping(Site)=1 then 'All' else Site end, FormID = case when grouping(FormID)=1 then 'All' else cast(FormID as varchar(100)) end, measure = count(NumberOfForms) FROM @CRFCount -- chose below GROUP BY Site, FormID with cube --(ms sql 2005) --group by grouping sets(Site, FormID, (Site, FormID), ()) --(ms sql 2008) ) AS BOM PIVOT (max(measure) FOR [Site] IN ([Site 1], [Site 2], [Site 3], [All])) as pv ( SELECT * FROM ( SELECT Site = case when grouping(Site)=1 then 'All' else Site end, FormID = case when grouping(FormID)=1 then 'All' else cast(FormID as varchar(100)) end, measure = count(NumberOfForms) FROM @CRFCount -- chose below GROUP BY Site, FormID with cube --(ms sql 2005) --group by grouping sets(Site, FormID, (Site, FormID), ()) --(ms sql 2008) ) AS BOM PIVOT (max(measure) FOR [Site] IN ([Site 1], [Site 2], [Site 3], [All])) as pv 

BEISPIELTABELLE

 SELECT * INTO #TEMP FROM ( SELECT 'Site 1' [Site], 'Form A' [FormID], 'Yes' Present UNION ALL SELECT 'Site 1', 'Form B', 'Yes' UNION ALL SELECT 'Site 1', 'Form C', 'Yes' UNION ALL SELECT 'Site 1', 'Form B', 'NO' UNION ALL SELECT 'Site 1', 'Form C', 'NO' UNION ALL SELECT 'Site 2', 'Form A', 'Yes' UNION ALL SELECT 'Site 2', 'Form A', 'Yes' UNION ALL SELECT 'Site 2', 'Form B', 'Yes' UNION ALL SELECT 'Site 2', 'Form B', 'NO' UNION ALL SELECT 'Site 2', 'Form C', 'Yes' UNION ALL SELECT 'Site 3', 'Form B', 'Yes' UNION ALL SELECT 'Site 3', 'Form A', 'Yes' UNION ALL SELECT 'Site 3', 'Form C', 'Yes' UNION ALL SELECT 'Site 3', 'Form A', 'Yes' )TAB ( SELECT * INTO #TEMP FROM ( SELECT 'Site 1' [Site], 'Form A' [FormID], 'Yes' Present UNION ALL SELECT 'Site 1', 'Form B', 'Yes' UNION ALL SELECT 'Site 1', 'Form C', 'Yes' UNION ALL SELECT 'Site 1', 'Form B', 'NO' UNION ALL SELECT 'Site 1', 'Form C', 'NO' UNION ALL SELECT 'Site 2', 'Form A', 'Yes' UNION ALL SELECT 'Site 2', 'Form A', 'Yes' UNION ALL SELECT 'Site 2', 'Form B', 'Yes' UNION ALL SELECT 'Site 2', 'Form B', 'NO' UNION ALL SELECT 'Site 2', 'Form C', 'Yes' UNION ALL SELECT 'Site 3', 'Form B', 'Yes' UNION ALL SELECT 'Site 3', 'Form A', 'Yes' UNION ALL SELECT 'Site 3', 'Form C', 'Yes' UNION ALL SELECT 'Site 3', 'Form A', 'Yes' )TAB 

1. Zeile und Spalte insgesamt

 -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] -- Since we need Total in last column, we append it at last SELECT @cols += ',[Total]' 

Sie können CUBE , um die Zeile und Spalte insgesamt beim Schwenken zu erhalten. Mehr über CUBE hier

 DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH CUBE ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ( DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH CUBE ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ( DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH CUBE ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ) x DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH CUBE ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ( DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH CUBE ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ) p DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH CUBE ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query 

2. Zeile nur

Sie können ROLLUP , um die Gesamtsumme zu erhalten.

 -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ( -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ( -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ) x -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ( -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query ) p -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY [SITE],FORMID WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID' EXEC SP_EXECUTESQL @query 

3. Spalte Gesamt nur

Ändere GROUP BY [SITE],FORMID zu GROUP BY FORMID,[SITE]

 -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] --Since we need Total in last column, we append it at last SELECT @cols += ',[Total]' DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY FORMID,[SITE] WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p WHERE FORMID <> ''Total'' ORDER BY FORMID' EXEC SP_EXECUTESQL @query ( -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] --Since we need Total in last column, we append it at last SELECT @cols += ',[Total]' DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY FORMID,[SITE] WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p WHERE FORMID <> ''Total'' ORDER BY FORMID' EXEC SP_EXECUTESQL @query ( -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] --Since we need Total in last column, we append it at last SELECT @cols += ',[Total]' DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY FORMID,[SITE] WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p WHERE FORMID <> ''Total'' ORDER BY FORMID' EXEC SP_EXECUTESQL @query ) x -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] --Since we need Total in last column, we append it at last SELECT @cols += ',[Total]' DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY FORMID,[SITE] WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p WHERE FORMID <> ''Total'' ORDER BY FORMID' EXEC SP_EXECUTESQL @query ( -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] --Since we need Total in last column, we append it at last SELECT @cols += ',[Total]' DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY FORMID,[SITE] WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p WHERE FORMID <> ''Total'' ORDER BY FORMID' EXEC SP_EXECUTESQL @query ) p -- Get the columns for dynamic pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']') FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV ORDER BY [Site] --Since we need Total in last column, we append it at last SELECT @cols += ',[Total]' DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT FORMID,' + @cols + ' FROM ( SELECT ISNULL([SITE],''Total'')[SITE], SUM(CNT)CNT , ISNULL(FORMID,''Total'')FORMID FROM ( SELECT DISTINCT [SITE],FORMID, COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT FROM #TEMP WHERE PRESENT=''YES'' )TAB GROUP BY FORMID,[SITE] WITH ROLLUP ) x PIVOT ( MIN(CNT) FOR [SITE] IN (' + @cols + ') ) p WHERE FORMID <> ''Total'' ORDER BY FORMID' EXEC SP_EXECUTESQL @query 

Nun, wenn du null mit null replace willst, kannst du den unten stehenden Code vor dem dynamischen Pivot verwenden.

 DECLARE @NulltoZeroCols NVARCHAR (MAX) SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+[Site]+'],0) AS ['+[Site]+']' FROM (SELECT DISTINCT [Site] FROM #TEMP)TAB ORDER BY [Site] FOR XML PATH('')),2,8000) SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]' 

Und in der heraus die meisten Abfrage von dynamischen Pivot, replace Sie @cols Variable mit @NullToZeroCols