行我有几个月。在列中,我有 1 到 31 天。我想在一个月的 31 天内所有销售的总和末尾添加一列。
SELECT * 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
此代码显示 31 天的按天数据,但不显示总和。
如果您希望
将其作为按月显示总数的列,不幸的是,没有简单的方法。你可以使用这样的东西:
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
请参阅带有演示的 SQL 小提琴
在这种情况下,动态 sql 更容易实现以透视数据。通过使用动态 SQL,您不必对值进行硬编码。
如果要使用动态 sql,则查询将类似于以下内容:
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)
请参阅带有演示的 SQL 小提琴
或者另一个建议,我可能是将您的列标题交替为Month
,然后您可以在每个月实施一个GROUP BY with ROLLUP
来获取总计行。
因此,您的新结果集将如下所示:
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 | ....
尝试 BETWEEN 条件
SELECT * FROM (SELECT DATENAME(month, date) AS SaleMonth,DATEPART(dd,date) AS SaleDay FROM EnquiryMaster) p PIVOT (COUNT (SaleDay) FOR SaleDay BETWEEN 1 and 31) AS pvt
尝试在子选择中添加一列:
SELECT * FROM (
SELECT DATENAME(month, date) AS SaleMonth,
DATEPART(dd,date) AS SaleDay,
COUNT(*) OVER (PARTITION BY DATENAME(month, date)) AS MonthlyTotal
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