By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SALESHS
(
IDAREA INT,
DATEREG [NVARCHAR](50) NOT NULL,
IDPROD [NVARCHAR](50) NOT NULL
);
INSERT INTO SALESHS
(
IDAREA, DATEREG, IDPROD
)
VALUES
(
1, '12/03/2019', 'xplpc'
),
(
1, '15/03/2019', 'ndtlctm'
),
(
2, '12/04/2019', 'wntd'
)
3 rows affected
--Build the column names for Pivot using dynamic SQL
DECLARE @YourChoice date
set @YourChoice = '2017/12/13'
declare @count int = 0
declare @columnstr varchar(max)
declare @columnpivot varchar(max)
declare @onecolumnname varchar(20)
set @columnstr = ''
set @columnpivot = ''
set @onecolumnname = ''
while @count <= DATEDIFF(MONTH,@YourChoice,GETDATE())
begin
set @onecolumnname = concat(cast(datename(month,dateadd(month,@count,@YourChoice)) as varchar(50)),cast(year(dateadd(month,@count,@YourChoice)) as varchar(10)))
set @columnstr = @columnstr + 'coalesce([' + @onecolumnname+ '],0) as '+@onecolumnname+', '
set @columnpivot = @columnpivot + '['+@onecolumnname+'], '
set @count = @count + 1
end
set @columnstr = left(@columnstr,len(@columnstr)-1)
set @columnpivot = '('+left(@columnpivot,len(@columnpivot)-1)+')'
--Pivot time!
declare @str varchar(max)
set @str =
'select IDAREA,' + @columnstr +' from (
select count(s.idarea) as amount,IDAREA,columnname from (
select *,datename(month,cast(substring(datereg,7,4)+''-''+substring(datereg,4,2)+''-''+substring(datereg,1,2) as datetime)) + SUBSTRING(datereg,7,4) as columnname
from SALESHS )s
group by IDAREA,columnname)s1
pivot
(
max(s1.amount)
for s1.columnname in '+@columnpivot+'
) p'
| IDAREA | December2017 | January2018 | February2018 | March2018 | April2018 | May2018 | June2018 | July2018 | August2018 | September2018 | October2018 | November2018 | December2018 | January2019 | February2019 | March2019 | April2019 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 |
| 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |