How label each output group type-CodePudding

I have a query like this:

Product

orderid  TypeId datefulfilled
17749    Spec   2022-10-11 18:35:25.000
17754    Spec   2022-10-12 18:35:25.000
17755    Spec   2022-10-12 18:35:25.000
17756    Spec   2022-10-12 18:35:25.000
17757    Spec   2022-10-16 18:35:25.000
17769    Spec   2022-11-24 18:35:25.000
17788    Spec   2022-12-12 18:35:25.000
17819    Spec   2022-12-19 18:35:25.000
17829    Spec   2022-12-19 18:35:25.000
17830    Spec   2022-01-08 18:35:25.000
17830    Cert   2022-01-08 18:35:25.000


select  
  count(distinct p.orderid) as overall_count, format(datefulfilled, 'yyyy/MM')
from Product p where datefulfilled <= dateadd(year,-1,getdate()) and typeId in ('Spec') group by format(datefulfilled,'yyyy/MM') order by format(datefulfilled,'yyyy/MM')

gives result counts like:

overall_count
2  2022/12
1  2023/01

How do I get it to label the individual output formatted like this (each output row has overall_count text preceding it):

overall_count 2  2022/12
overall_count 1  2022/12

I'm having trouble finding any info on how to do this searching the internet. This will help people using the report do their calculations.

CodePudding user response：

concat() is a nice fit here.

Just an aside, format() has some great features, but the performance is dreadful (it should be used sparingly). Notice that I use convert(varchar(7),datefulfilled,111) instead

Updated: used a CROSS APPLY to reduce the number of date conversions

Example

select NewValue = concat('overall_count'
                         ,
                          ' '
                         ,
                          count(distinct p.orderid)
                         ,' '
                         ,yyyymm
                        )
from Product p 
cross apply ( values ( convert(varchar(7),datefulfilled,111) ) ) b(yyyymm) 
where datefulfilled <= dateadd(year,-1,getdate()) 
  and typeId in ('Spec') 
  group by yyyymm
  order by yyyymm

Results

NewValue
overall_count 1 2022/01