add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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, ''
);

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