StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POpivot in sql - generate grandtotal column from pivot columns
text
Body
copied!<p>Below query works fine and i get the pivot table as required. The dollar amounts show up fine from January to December. Now, how would i add a total column that would give me a row total from January to December? Since these months column are created using pivot, i am not sure how to add the values for all months and display that in a new column. Could you please help? Let me know if you need more clarification on what i am looking for.</p> <p>Thanks in advance!</p> <pre><code>CREATE TABLE #t ( Region varchar(50), Area varchar(255), Market varchar(255), MediaType varchar(50), Campaign varchar(255), Description varchar(255), TotalCost money, MonthDesc nvarchar(50), [Year] int, MonthNumber int ) DECLARE @List VARCHAR(8000), @SQL VARCHAR(8000) SET @List = '' SET @SQL = '' INSERT INTO #t SELECT r.RegionName, d.DistrictName, dm.DMAName, mt.MediaTypeDesc, pn.PlanName, pd.VendorPreference, SUM(pd.DropAmount) AS TotalCost, DATENAME(MONTH, pd.StartDate), p.[Year], DATEPART(MONTH, pd.StartDate) FROM PlanDetails pd INNER JOIN Plans p ON p.PlanId = pd.PlanId INNER JOIN PlanNames pn ON pn.PlanNameId = p.PlanNameId INNER JOIN DMA_Plans dp ON p.PlanId = dp.PlanId INNER JOIN DMA dm ON dm.DMAId = dp.DMAId INNER JOIN Centers c ON c.DMAId = dm.DMAId INNER JOIN Districts d ON d.DistrictId = c.DistrictId INNER JOIN Regions r ON r.RegionId = d.RegionId INNER JOIN MediaTypes mt ON mt.MediaTypeId = pd.MediaTypeId WHERE c.OrganizationId = 2 AND p.[Year] = @Year AND pd.StartDate IS NOT NULL AND p.Active = 1 AND c.DMAId IS NOT NULL AND pd.MediaTypeId IS NOT NULL GROUP BY r.RegionName, d.DistrictName, dm.DMAName, mt.MediaTypeDesc, pn.PlanName, pd.VendorPreference, pd.StartDate, p.[Year] DECLARE @TblY TABLE(ODate nvarchar(50), oYear int, oMonthOrder int) INSERT @TblY SELECT DISTINCT MonthDesc, [Year], MonthNumber FROM [#t] ORDER BY MonthNumber SELECT @List = ISNULL(@List,'') + CASE WHEN ISNULL(@List,'') = '' THEN '[' + ODate + ']' ELSE ',[' + ODate + ']' end FROM @TblY ORDER BY oMonthOrder SET @SQL = 'Select Region, Area, Market, MediaType, Campaign, Description, ' + @List + char(13) SET @SQL = @SQL + 'From (Select Region, Area, Market, MediaType, Campaign, Description, MonthDesc, TotalCost ' + char(13) SET @SQL = @SQL + 'FROM #t F) P ' + char(13) SET @SQL = @SQL + 'Pivot (SUM(TotalCost) For MonthDesc In (' + @List + ')) as Pvt' + char(13) PRINT @SQL EXEC (@SQL) DROP TABLE #t </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload