StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you want this as a column to display the total by month, there unfortunately is no easy way. You can use something like this:</p> <pre><code>SELECT SaleMonth, [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], ([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]) TotalMonth FROM ( SELECT DATENAME(month, date) AS SaleMonth, DATEPART(dd,date) AS SaleDay FROM EnquiryMaster ) p PIVOT ( COUNT (SaleDay) FOR SaleDay IN ( [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] ) ) AS pvt </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/0e254/13" rel="nofollow">SQL Fiddle with Demo</a></p> <p>This might be a case when dynamic sql would be easier to implement to pivot the data. By using dynamic SQL, you would not have to hard-code the values.</p> <p>If you were to use dynamic sql your query would be similar to this:</p> <pre><code>DECLARE @cols AS NVARCHAR(MAX), @colsTotal AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(DATEPART(dd,date)) from EnquiryMaster group by DATEPART(dd,date) order by DATEPART(dd,date) FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @colsTotal = STUFF((SELECT distinct '+' + QUOTENAME(DATEPART(dd,date)) from EnquiryMaster FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT SaleMonth,' + @cols + ', '+ @colsTotal+' as GrandTotal from ( SELECT DATENAME(month, date) AS SaleMonth, DATEPART(dd,date) AS SaleDay FROM EnquiryMaster ) x pivot ( count(SaleDay) for SaleDay in (' + @cols + ') ) p ' execute(@query) </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/0e254/12" rel="nofollow">SQL Fiddle with Demo</a></p> <p>Or another suggestion, I might have would be to alternate your columns headers to be the <code>Month</code> and then you can implement a <code>GROUP BY with ROLLUP</code> on each month to get a totals row.</p> <p>So your new result set would look like this:</p> <pre><code>Day | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec ------------------------------------------------------------------------------- 1 | 12 | 34 | 5 | 3 | 3 | 2 | 32 | 0 | 9 | 87 | 23 | 54 Total | .... </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