SQL Server How to name subtotalled rows using roll up in SQL [duplicate]

lvmkulzt 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(116)

This question already has answers here:

Alias names to with rollup in SQL queries? (3 answers)
Closed 28 days ago.

select cast(csdate as varchar(10)), 
       sum(sales)
from fact
group by rollup (datepart(year,csdate),
          datepart(week,csdate), 
          csdate)

Here I want the null to be replaced by the week range for that week suppose '2010-01-10 to 2010-01-12' thus for each partition's subtotal and lastly for the grandtotal as 'total'. Also I want suggestion on how can I name that row with the actual week range like in the first case, there are only two dates present for week(refer calendar) but I would like that subtotal to show with legend '2009-12-27 to 2010-01-03' representing that full week ka subtotal is this. Also, since for the first value the week is going to last year, and if I am interested to show from 1st Jan 2010, can I do that. See the existing result for reference.

sql-server

来源：https://stackoverflow.com/questions/77117615/how-to-name-subtotalled-rows-using-roll-up-in-sql

1条答案

按热度按时间

r3i60tvu1#

You will want to use COALESCE , like

select CASE 
           WHEN grouping(datepart(year,csdate)) = 1 THEN 'year_label' 
           WHEN grouping(datepart(week,csdate)) = 1 THEN 'week_label'  
           ELSE COALESCE(cast(csdate as varchar(10)), '<yourvaluehere>')
       END
from fact
group by rollup (datepart(year,csdate),
          datepart(week,csdate), 
          csdate)

You have three levels of rollup: a date level, a week level and a year level due to having three levels of rollup. You need to differentiate between them.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to name subtotalled rows using roll up in SQL [duplicate]

1条答案

相关问题

热门标签

最新问答