Stored procedure has 2 date parameters:
datefrom '2020-01-01'
dateto '2020-12-31'
I have to output the year-months per column in this selection.
I extracted a string with all these months:
declare @cols nvarchar(max)
set @cols = '[2020-01],[2020-02],[2020-03],[2020-04],[2020-05],[2020-06],[2020-07],[2020-08],[2020-09],[2020-10],[2020-11],[2020-12]'
select @cols
Output is a single column with @cols in it, but what I need is (in this case) 12 columns with first column [2020-01], second column [2020-02] etc.
(@cols is not static, it varies with the select on the dates as the number of columns)
I have tried this with a pivot, but since I have no second value to count this will not work.
I really want to avoid having to declare upfront a static number of columns since there is no limit on the number of columns.
Is this even possible ?
What I have now :
DECLARE @FromDate nvarchar(30) = N'19000101',
@ToDate nvarchar(30) = N'99991231',
@counter int,
@yearmonth nchar(9)
SET @FromDate = CAST(ISNULL(@FromDate, GETDATE()) AS date);
SET @ToDate = CAST(ISNULL(@ToDate, GETDATE()) AS date);
DECLARE @no_months int;
SELECT @no_months = DATEDIFF(mm, @FromDate, @ToDate)
DECLARE @StartMonth nchar(7);
SELCET @StartMonth = CONVERT(char(7), DATEADD(DAY, -(DAY(@fromdate) - 1), @fromdate), 120)
DECLARE @COLUMN1 NCHAR(7), @COLUMN2 NCHAR(7),
@COLUMN3 NCHAR(7), @COLUMN4 NCHAR(7),
@COLUMN5 NCHAR(7), @COLUMN6 NCHAR(7),
@COLUMN7 NCHAR(7), @COLUMN8 NCHAR(7),
@COLUMN9 NCHAR(7), @COLUMN10 NCHAR(7),
@COLUMN11 NCHAR(7), @COLUMN12 NCHAR(7),
@COLUMN13 NCHAR(7), @COLUMN14 NCHAR(7),
@COLUMN15 NCHAR(7), @COLUMN16 NCHAR(7),
@COLUMN17 NCHAR(7), @COLUMN18 NCHAR(7),
@COLUMN19 NCHAR(7), @COLUMN20 NCHAR(7),
@COLUMN21 NCHAR(7), @COLUMN22 NCHAR(7),
@COLUMN23 NCHAR(7), @COLUMN24 NCHAR(7),
@COLUMN25 NCHAR(7), @COLUMN26 NCHAR(7),
@COLUMN27 NCHAR(7), @COLUMN28 NCHAR(7),
@COLUMN29 NCHAR(7), @COLUMN30 NCHAR(7)
SET @Counter = 0
WHILE @counter <= @no_months
BEGIN
SELECT @yearmonth = convert(char(7),DATEADD (MONTH,@COUNTER,@FromDate),120)
IF @COUNTER=1 SET @COLUMN1 = @yearmonth
IF @COUNTER=2 SET @COLUMN2 = @yearmonth
IF @COUNTER=3 SET @COLUMN3 = @yearmonth
IF @COUNTER=4 SET @COLUMN4 = @yearmonth
IF @COUNTER=5 SET @COLUMN5 = @yearmonth
IF @COUNTER=6 SET @COLUMN6 = @yearmonth
IF @COUNTER=7 SET @COLUMN7 = @yearmonth
IF @COUNTER=8 SET @COLUMN8 = @yearmonth
IF @COUNTER=9 SET @COLUMN9 = @yearmonth
IF @COUNTER=10 SET @COLUMN10 = @yearmonth
IF @COUNTER=11 SET @COLUMN11 = @yearmonth
IF @COUNTER=12 SET @COLUMN12 = @yearmonth
IF @COUNTER=13 SET @COLUMN13 = @yearmonth
IF @COUNTER=14 SET @COLUMN14 = @yearmonth
IF @COUNTER=15 SET @COLUMN15 = @yearmonth
IF @COUNTER=16 SET @COLUMN16 = @yearmonth
IF @COUNTER=17 SET @COLUMN17 = @yearmonth
IF @COUNTER=18 SET @COLUMN18 = @yearmonth
IF @COUNTER=19 SET @COLUMN19 = @yearmonth
IF @COUNTER=20 SET @COLUMN20 = @yearmonth
IF @COUNTER=21 SET @COLUMN21 = @yearmonth
IF @COUNTER=22 SET @COLUMN22 = @yearmonth
IF @COUNTER=23 SET @COLUMN23 = @yearmonth
IF @COUNTER=24 SET @COLUMN24 = @yearmonth
IF @COUNTER=25 SET @COLUMN25 = @yearmonth
IF @COUNTER=26 SET @COLUMN26 = @yearmonth
IF @COUNTER=27 SET @COLUMN27 = @yearmonth
IF @COUNTER=28 SET @COLUMN28 = @yearmonth
IF @COUNTER=29 SET @COLUMN29 = @yearmonth
IF @COUNTER=30 SET @COLUMN30 = @yearmonth
--insert into #periodesKOP
--values (@yearmonth)
SET @Counter=@Counter+1
END
--DECLARE @colsKOP AS NVARCHAR(MAX)
--select @colsKOP = STUFF((SELECT ',' + QUOTENAME(JaarMaand)
-- from #periodesKOP
-- group by JaarMaand
-- order by JaarMaand
-- FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)')
-- ,1,1,'')
SELECT 'Artikel','Omschrijving','Voorraad','Aantal Maanden','Gem.snelheid',@COLUMN1,@COLUMN2,@COLUMN3,@COLUMN4,@COLUMN5,
@COLUMN6,@COLUMN7,@COLUMN8,@COLUMN9,@COLUMN10,@COLUMN11,@COLUMN12,@COLUMN13,@COLUMN14,@COLUMN15,@COLUMN16,@COLUMN16,@COLUMN17,
@COLUMN18,@COLUMN19,@COLUMN20,@COLUMN21,@COLUMN22,@COLUMN23,@COLUMN24,@COLUMN25,@COLUMN26,@COLUMN27,@COLUMN28,@COLUMN29,@COLUMN30
But disadvantage : it is static-> always max 30 columns and what if I have less then 30 months.
The date selection can result in 5 months or 25 months. As you can see in the example I initially had a temp table #periodes with all the months from the date-selection and put them in @cols. But hen got stuck to get all content of @cols in a select to columns.
Not too sure what you are trying to accomplish but what you want should be possible with dynamic sql e.g. here
declare @cols nvarchar(max)
set @cols = '
select
''2020-01'' as [2020-01]
,''2020-02'' as [2020-02]
,''2020-03'' as [2020-03]
,''2020-04'' as [2020-04]
,''2020-05'' as [2020-05]
,''2020-06'' as [2020-06]
,''2020-07'' as [2020-07]
,''2020-08'' as [2020-08]
,''2020-09'' as [2020-09]
,''2020-10'' as [2020-10]
,''2020-11'' as [2020-11]
,''2020-12'' as [2020-12]'
exec(@cols)
Here is your code with some ugly updates that im sure you will be able to modify
declare @FromDate nvarchar(30) = N'19000101',
@ToDate nvarchar(30) = N'99991231',
@counter int,
@yearmonth nchar(9)
SET @FromDate = CAST(ISNULL(@FromDate, GETDATE()) AS date);
SET @ToDate = CAST(ISNULL(@ToDate, GETDATE()) AS date);
Declare @no_months int ; SELECT @no_months=DATEDIFF(mm, @FromDate, @ToDate)
Declare @StartMonth nchar(7); select @StartMonth=convert(char(7),dATEADD(DAY, -(DAY(@fromdate) - 1), @fromdate),120)
declare @cols nvarchar(2000)
DECLARE @COLUMN1 NCHAR(7),
@COLUMN2 NCHAR(7),@COLUMN3 NCHAR(7),@COLUMN4 NCHAR(7),@COLUMN5 NCHAR(7),@COLUMN6 NCHAR(7),@COLUMN7 NCHAR(7),@COLUMN8 NCHAR(7),
@COLUMN9 NCHAR(7),@COLUMN10 NCHAR(7),@COLUMN11 NCHAR(7),@COLUMN12 NCHAR(7),@COLUMN13 NCHAR(7),@COLUMN14 NCHAR(7),
@COLUMN15 NCHAR(7),@COLUMN16 NCHAR(7),@COLUMN17 NCHAR(7),@COLUMN18 NCHAR(7),@COLUMN19 NCHAR(7),@COLUMN20 NCHAR(7),
@COLUMN21 NCHAR(7),@COLUMN22 NCHAR(7),@COLUMN23 NCHAR(7),@COLUMN24 NCHAR(7),@COLUMN25 NCHAR(7),@COLUMN26 NCHAR(7),
@COLUMN27 NCHAR(7),@COLUMN28 NCHAR(7),@COLUMN29 NCHAR(7),@COLUMN30 NCHAR(7)
SET @Counter=0
WHILE @counter <= @no_months
BEGIN
SELECT @yearmonth = convert(char(7),DATEADD (MONTH,@COUNTER,@FromDate),120)
IF @COUNTER=1 SET @COLUMN1 = @yearmonth
IF @COUNTER=2 SET @COLUMN2 = @yearmonth
IF @COUNTER=3 SET @COLUMN3 = @yearmonth
IF @COUNTER=4 SET @COLUMN4 = @yearmonth
IF @COUNTER=5 SET @COLUMN5 = @yearmonth
IF @COUNTER=6 SET @COLUMN6 = @yearmonth
IF @COUNTER=7 SET @COLUMN7 = @yearmonth
IF @COUNTER=8 SET @COLUMN8 = @yearmonth
IF @COUNTER=9 SET @COLUMN9 = @yearmonth
IF @COUNTER=10 SET @COLUMN10 = @yearmonth
IF @COUNTER=11 SET @COLUMN11 = @yearmonth
IF @COUNTER=12 SET @COLUMN12 = @yearmonth
IF @COUNTER=13 SET @COLUMN13 = @yearmonth
IF @COUNTER=14 SET @COLUMN14 = @yearmonth
IF @COUNTER=15 SET @COLUMN15 = @yearmonth
IF @COUNTER=16 SET @COLUMN16 = @yearmonth
IF @COUNTER=17 SET @COLUMN17 = @yearmonth
IF @COUNTER=18 SET @COLUMN18 = @yearmonth
IF @COUNTER=19 SET @COLUMN19 = @yearmonth
IF @COUNTER=20 SET @COLUMN20 = @yearmonth
IF @COUNTER=21 SET @COLUMN21 = @yearmonth
IF @COUNTER=22 SET @COLUMN22 = @yearmonth
IF @COUNTER=23 SET @COLUMN23 = @yearmonth
IF @COUNTER=24 SET @COLUMN24 = @yearmonth
IF @COUNTER=25 SET @COLUMN25 = @yearmonth
IF @COUNTER=26 SET @COLUMN26 = @yearmonth
IF @COUNTER=27 SET @COLUMN27 = @yearmonth
IF @COUNTER=28 SET @COLUMN28 = @yearmonth
IF @COUNTER=29 SET @COLUMN29 = @yearmonth
IF @COUNTER=30 SET @COLUMN30 = @yearmonth
--insert into #periodesKOP
--values (@yearmonth)
SET @Counter=@Counter+1
END
--DECLARE @colsKOP AS NVARCHAR(MAX)
--select @colsKOP = STUFF((SELECT ',' + QUOTENAME(JaarMaand)
-- from #periodesKOP
-- group by JaarMaand
-- order by JaarMaand
-- FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)')
-- ,1,1,'')
set @cols = concat('''' + @COLUMN1+ '''' +','
,''''+ @COLUMN2 + '''' +','
,''''+ @COLUMN3 + '''' +','
,''''+ @COLUMN4 + '''' +','
,''''+ @COLUMN5 + '''' +','
,''''+ @COLUMN6 + '''' +','
,''''+ @COLUMN7 + '''' +','
,''''+ @COLUMN8 + '''' +','
,''''+ @COLUMN9 + '''' +','
,''''+ @COLUMN10 + '''' +','
,''''+ @COLUMN11 + '''' +','
,''''+ @COLUMN12 + '''' +','
,''''+ @COLUMN13 + '''' +','
,''''+ @COLUMN14 + '''' +','
,''''+ @COLUMN15 + '''' +','
,''''+ @COLUMN16 + '''' +','
,''''+ @COLUMN16 + '''' +','
,''''+ @COLUMN17 + '''' +','
,''''+ @COLUMN18 + '''' +','
,''''+ @COLUMN19 + '''' +','
,''''+ @COLUMN20 + '''' +','
,''''+ @COLUMN21 + '''' +','
,''''+ @COLUMN22 + '''' +','
,''''+ @COLUMN23 + '''' +','
,''''+ @COLUMN24 + '''' +','
,''''+ @COLUMN25 + '''' +','
,''''+ @COLUMN26 + '''' +','
,''''+ @COLUMN27 + '''' +','
,''''+ @COLUMN28 + '''' +','
,''''+ @COLUMN29 + '''' +','
,''''+ @COLUMN30 + '''' )
set @cols = concat('SELECT ''Artikel'',''Omschrijving'',''Voorraad'',''Aantal Maanden'',''Gem.snelheid'',' , @cols)
set @cols = left(@cols,len(@cols)-1)
select @cols
exec( @cols)