Skip to content
Advertisement

SQL get rows to colums only for 1 row. Pivot not possible

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.

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 @cols
exec( @cols)
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement