SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

drop table if exists c_item
create table c_item(item_code int, site_code int, date_field varchar(10))

insert into c_item
select 1,1,'2021-01-01'
union
select 2,1,'2021-03-01'
union
select 3,1,'2021-04-01'
union
select 4,2,'2021-04-01'
4 rows affected

create or alter  procedure pivot_table_with_user_input
(
@from_Date varchar(10),
@to_Date varchar(10)
)
as
begin

DROP TABLE IF EXISTS [CalendarTable]
CREATE TABLE [CalendarTable]
(
    [CalendarDate] varchar(10)
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = (select dateadd(yy, -10, convert(varchar(10), GETDATE(), 121)))	--10 years back from the current date
SET @EndDate = DATEADD(yy, 10, @StartDate)											--10 years in the future from the current date

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO [CalendarTable]
    SELECT convert(varchar(10), convert(date, @StartDate), 121)
               
    SET @StartDate = DATEADD(dd, 1, @StartDate)
END

declare @cols nvarchar(max) = ''
set @cols = STUFF(
					 (	select ',' + quotename(convert(varchar(10), t.CalendarDate))
						from CalendarTable t
						where t.CalendarDate between convert(date, @from_Date) and convert(date, @to_Date)
						FOR XML PATH(''), TYPE
					 ).value('.', 'nvarchar(max)'), 1, 1, ''
				);

select @cols column_value


declare @sql nvarchar(max) = '
select site_code, '+@cols+'
From
(
	Select site_code,item_code, date_field
	from c_item
)PVT
Pivot
(
	Count(item_code)for date_field in ('+@cols+')
)P
'
print '-------------------Actual query below--------------------------'
print @sql

exec sp_executesql @sql

end
exec pivot_table_with_user_input '20201201', '20210315'


column_value


[2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04],[2020-12-05],[2020-12-06],[2020-12-07],[2020-12-08],[2020-12-09],[2020-12-10],[2020-12-11],[2020-12-12],[2020-12-13],[2020-12-14],[2020-12-15],[2020-12-16],[2020-12-17],[2020-12-18],[2020-12-19],[2020-12-20],[2020-12-21],[2020-12-22],[2020-12-23],[2020-12-24],[2020-12-25],[2020-12-26],[2020-12-27],[2020-12-28],[2020-12-29],[2020-12-30],[2020-12-31],[2021-01-01],[2021-01-02],[2021-01-03],[2021-01-04],[2021-01-05],[2021-01-06],[2021-01-07],[2021-01-08],[2021-01-09],[2021-01-10],[2021-01-11],[2021-01-12],[2021-01-13],[2021-01-14],[2021-01-15],[2021-01-16],[2021-01-17],[2021-01-18],[2021-01-19],[2021-01-20],[2021-01-21],[2021-01-22],[2021-01-23],[2021-01-24],[2021-01-25],[2021-01-26],[2021-01-27],[2021-01-28],[2021-01-29],[2021-01-30],[2021-01-31],[2021-02-01],[2021-02-02],[2021-02-03],[2021-02-04],[2021-02-05],[2021-02-06],[2021-02-07],[2021-02-08],[2021-02-09],[2021-02-10],[2021-02-11],[2021-02-12],[2021-02-13],[2021-02-14],[2021-02-15],[2021-02-16],[2021-02-17],[2021-02-18],[2021-02-19],[2021-02-20],[2021-02-21],[2021-02-22],[2021-02-23],[2021-02-24],[2021-02-25],[2021-02-26],[2021-02-27],[2021-02-28],[2021-03-01],[2021-03-02],[2021-03-03],[2021-03-04],[2021-03-05],[2021-03-06],[2021-03-07],[2021-03-08],[2021-03-09],[2021-03-10],[2021-03-11],[2021-03-12],[2021-03-13],[2021-03-14],[2021-03-15]




site_code
2020-12-01
2020-12-02
2020-12-03
2020-12-04
2020-12-05
2020-12-06
2020-12-07
2020-12-08
2020-12-09
2020-12-10
2020-12-11
2020-12-12
2020-12-13
2020-12-14
2020-12-15
2020-12-16
2020-12-17
2020-12-18
2020-12-19
2020-12-20
2020-12-21
2020-12-22
2020-12-23
2020-12-24
2020-12-25
2020-12-26
2020-12-27
2020-12-28
2020-12-29
2020-12-30
2020-12-31
2021-01-01
2021-01-02
2021-01-03
2021-01-04
2021-01-05
2021-01-06
2021-01-07
2021-01-08
2021-01-09
2021-01-10
2021-01-11
2021-01-12
2021-01-13
2021-01-14
2021-01-15
2021-01-16
2021-01-17
2021-01-18
2021-01-19
2021-01-20
2021-01-21
2021-01-22
2021-01-23
2021-01-24
2021-01-25
2021-01-26
2021-01-27
2021-01-28
2021-01-29
2021-01-30
2021-01-31
2021-02-01
2021-02-02
2021-02-03
2021-02-04
2021-02-05
2021-02-06
2021-02-07
2021-02-08
2021-02-09
2021-02-10
2021-02-11
2021-02-12
2021-02-13
2021-02-14
2021-02-15
2021-02-16
2021-02-17
2021-02-18
2021-02-19
2021-02-20
2021-02-21
2021-02-22
2021-02-23
2021-02-24
2021-02-25
2021-02-26
2021-02-27
2021-02-28
2021-03-01
2021-03-02
2021-03-03
2021-03-04
2021-03-05
2021-03-06
2021-03-07
2021-03-08
2021-03-09
2021-03-10
2021-03-11
2021-03-12
2021-03-13
2021-03-14
2021-03-15


1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0

2
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0


-------------------Actual query below--------------------------

select site_code, [2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04],[2020-12-05],[2020-12-06],[2020-12-07],[2020-12-08],[2020-12-09],[2020-12-10],[2020-12-11],[2020-12-12],[2020-12-13],[2020-12-14],[2020-12-15],[2020-12-16],[2020-12-17],[2020-12-18],[2020-12-19],[2020-12-20],[2020-12-21],[2020-12-22],[2020-12-23],[2020-12-24],[2020-12-25],[2020-12-26],[2020-12-27],[2020-12-28],[2020-12-29],[2020-12-30],[2020-12-31],[2021-01-01],[2021-01-02],[2021-01-03],[2021-01-04],[2021-01-05],[2021-01-06],[2021-01-07],[2021-01-08],[2021-01-09],[2021-01-10],[2021-01-11],[2021-01-12],[2021-01-13],[2021-01-14],[2021-01-15],[2021-01-16],[2021-01-17],[2021-01-18],[2021-01-19],[2021-01-20],[2021-01-21],[2021-01-22],[2021-01-23],[2021-01-24],[2021-01-25],[2021-01-26],[2021-01-27],[2021-01-28],[2021-01-29],[2021-01-30],[2021-01-31],[2021-02-01],[2021-02-02],[2021-02-03],[2021-02-04],[2021-02-05],[2021-02-06],[2021-02-07],[2021-02-08],[2021-02-09],[2021-02-10],[2021-02-11],[2021-02-12],[2021-02-13],[2021-02-14],[2021-02-15],[2021-02-16],[2021-02-17],[2021-02-18],[2021-02-19],[2021-02-20],[2021-02-21],[2021-02-22],[2021-02-23],[2021-02-24],[2021-02-25],[2021-02-26],[2021-02-27],[2021-02-28],[2021-03-01],[2021-03-02],[2021-03-03],[2021-03-04],[2021-03-05],[2021-03-06],[2021-03-07],[2021-03-08],[2021-03-09],[2021-03-10],[2021-03-11],[2021-03-12],[2021-03-13],[2021-03-14],[2021-03-15]
From
(
	Select site_code,item_code, date_field
	from c_item
)PVT
Pivot
(
	Count(item_code)for date_field in ([2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04],[2020-12-05],[2020-12-06],[2020-12-07],[2020-12-08],[2020-12-09],[2020-12-10],[2020-12-11],[2020-12-12],[2020-12-13],[2020-12-14],[2020-12-15],[2020-12-16],[2020-12-17],[2020-12-18],[2020-12-19],[2020-12-20],[2020-12-21],[2020-12-22],[2020-12-23],[2020-12-24],[2020-12-25],[2020-12-26],[2020-12-27],[2020-12-28],[2020-12-29],[2020-12-30],[2020-12-31],[2021-01-01],[2021-01-02],[2021-01-03],[2021-01-04],[2021-01-05],[2021-01-06],[2021-01-07],[2021-01-08],[2021-01-09],[2021-01-10],[2021-01-11],[2021-01-12],[2021-01-13],[2021-01-14],[2021-01-15],[2021-01-16],[2021-01-17],[2021-01-18],[2021-01-19],[2021-01-20],[2021-01-21],[2021-01-22],[2021-01-23],[2021-01-24],[2021-01-25],[2021-01-26],[2021-01-27],[2021-01-28],[2021-01-29],[2021-01-30],[2021-01-31],[2021-02-01],[2021-02-02],[2021-02-03],[2021-02-04],[2021-02-05],[2021-02-06],[2021-02-07],[2021-02-08],[2021-02-09],[2021-02-10],[2021-02-11],[2021-02-12],[2021-02-13],[2021-02-14],[2021-02-15],[2021-02-16],[2021-02-17],[2021-02-18],[2021-02-19],[2021-02-20],[2021-02-21],[2021-02-22],[2021-02-23],[2021-02-24],[2021-02-25],[2021-02-26],[2021-02-27],[2021-02-28],[2021-03-01],[2021-03-02],[2021-03-03],[2021-03-04],[2021-03-05],[2021-03-06],[2021-03-07],[2021-03-08],[2021-03-09],[2021-03-10],[2021-03-11],[2021-03-12],[2021-03-13],[2021-03-14],[2021-03-15])
)P

site_code	2020-12-01	2020-12-02	2020-12-03	2020-12-04	2020-12-05	2020-12-06	2020-12-07	2020-12-08	2020-12-09	2020-12-10	2020-12-11	2020-12-12	2020-12-13	2020-12-14	2020-12-15	2020-12-16	2020-12-17	2020-12-18	2020-12-19	2020-12-20	2020-12-21	2020-12-22	2020-12-23	2020-12-24	2020-12-25	2020-12-26	2020-12-27	2020-12-28	2020-12-29	2020-12-30	2020-12-31	2021-01-01	2021-01-02	2021-01-03	2021-01-04	2021-01-05	2021-01-06	2021-01-07	2021-01-08	2021-01-09	2021-01-10	2021-01-11	2021-01-12	2021-01-13	2021-01-14	2021-01-15	2021-01-16	2021-01-17	2021-01-18	2021-01-19	2021-01-20	2021-01-21	2021-01-22	2021-01-23	2021-01-24	2021-01-25	2021-01-26	2021-01-27	2021-01-28	2021-01-29	2021-01-30	2021-01-31	2021-02-01	2021-02-02	2021-02-03	2021-02-04	2021-02-05	2021-02-06	2021-02-07	2021-02-08	2021-02-09	2021-02-10	2021-02-11	2021-02-12	2021-02-13	2021-02-14	2021-02-15	2021-02-16	2021-02-17	2021-02-18	2021-02-19	2021-02-20	2021-02-21	2021-02-22	2021-02-23	2021-02-24	2021-02-25	2021-02-26	2021-02-27	2021-02-28	2021-03-01	2021-03-02	2021-03-03	2021-03-04	2021-03-05	2021-03-06	2021-03-07	2021-03-08	2021-03-09	2021-03-10	2021-03-11	2021-03-12	2021-03-13	2021-03-14	2021-03-15
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

site_code	2020-12-01	2020-12-02	2020-12-03	2020-12-04	2020-12-05	2020-12-06	2020-12-07	2020-12-08	2020-12-09	2020-12-10	2020-12-11	2020-12-12	2020-12-13	2020-12-14	2020-12-15	2020-12-16	2020-12-17	2020-12-18	2020-12-19	2020-12-20	2020-12-21	2020-12-22	2020-12-23	2020-12-24	2020-12-25	2020-12-26	2020-12-27	2020-12-28	2020-12-29	2020-12-30	2020-12-31	2021-01-01	2021-01-02	2021-01-03	2021-01-04	2021-01-05	2021-01-06	2021-01-07	2021-01-08	2021-01-09	2021-01-10	2021-01-11	2021-01-12	2021-01-13	2021-01-14	2021-01-15	2021-01-16	2021-01-17	2021-01-18	2021-01-19	2021-01-20	2021-01-21	2021-01-22	2021-01-23	2021-01-24	2021-01-25	2021-01-26	2021-01-27	2021-01-28	2021-01-29	2021-01-30	2021-01-31	2021-02-01	2021-02-02	2021-02-03	2021-02-04	2021-02-05	2021-02-06	2021-02-07	2021-02-08	2021-02-09	2021-02-10	2021-02-11	2021-02-12	2021-02-13	2021-02-14	2021-02-15	2021-02-16	2021-02-17	2021-02-18	2021-02-19	2021-02-20	2021-02-21	2021-02-22	2021-02-23	2021-02-24	2021-02-25	2021-02-26	2021-02-27	2021-02-28	2021-03-01	2021-03-02	2021-03-03	2021-03-04	2021-03-05	2021-03-06	2021-03-07	2021-03-08	2021-03-09	2021-03-10	2021-03-11	2021-03-12	2021-03-13	2021-03-14	2021-03-15
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

site_code	2020-12-01	2020-12-02	2020-12-03	2020-12-04	2020-12-05	2020-12-06	2020-12-07	2020-12-08	2020-12-09	2020-12-10	2020-12-11	2020-12-12	2020-12-13	2020-12-14	2020-12-15	2020-12-16	2020-12-17	2020-12-18	2020-12-19	2020-12-20	2020-12-21	2020-12-22	2020-12-23	2020-12-24	2020-12-25	2020-12-26	2020-12-27	2020-12-28	2020-12-29	2020-12-30	2020-12-31	2021-01-01	2021-01-02	2021-01-03	2021-01-04	2021-01-05	2021-01-06	2021-01-07	2021-01-08	2021-01-09	2021-01-10	2021-01-11	2021-01-12	2021-01-13	2021-01-14	2021-01-15	2021-01-16	2021-01-17	2021-01-18	2021-01-19	2021-01-20	2021-01-21	2021-01-22	2021-01-23	2021-01-24	2021-01-25	2021-01-26	2021-01-27	2021-01-28	2021-01-29	2021-01-30	2021-01-31	2021-02-01	2021-02-02	2021-02-03	2021-02-04	2021-02-05	2021-02-06	2021-02-07	2021-02-08	2021-02-09	2021-02-10	2021-02-11	2021-02-12	2021-02-13	2021-02-14	2021-02-15	2021-02-16	2021-02-17	2021-02-18	2021-02-19	2021-02-20	2021-02-21	2021-02-22	2021-02-23	2021-02-24	2021-02-25	2021-02-26	2021-02-27	2021-02-28	2021-03-01	2021-03-02	2021-03-03	2021-03-04	2021-03-05	2021-03-06	2021-03-07	2021-03-08	2021-03-09	2021-03-10	2021-03-11	2021-03-12	2021-03-13	2021-03-14	2021-03-15
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0