Stored procedure has 2 date parameters:
x
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 @colsOutput 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,@COLUMN30But 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.
Advertisement
Answer
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 @colsexec( @cols)