Convert SQL Rows to Columns

ConvertSQLRowsToColumns.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LotNo](
                [IDNo] [bigint] IDENTITY(1,1) NOT NULL,
                [LotNo] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into LotNo (LotNo)
select 'A'
union all
select 'B'
union all
Select 'C'
union all
select 'D'
union all
Select 'E'
union all
select 'F'
union all
Select 'G'
union all
select 'H'
union all
Select 'I'
union all
select 'J'
union all
Select 'K'
union all
select 'L'
union all
Select 'M'
union all
select 'N'
union all
Select 'O'
union all
select 'P'
union all
Select 'Q'
union all
select 'R'
union all
Select 'S'
union all
select 'T'
union all
Select 'U'
union all
select 'V'
union all
Select 'W'
union all
select 'X'
union all
Select 'Y'
union all
select 'Z'

select
      LotNo
    , row_number() over ( order by LotNo )
   from LotNo
   
select
    LotNo
  , ( row_number() over ( order by LotNo ) + 2 ) % 3 as Remainder
  from
    lotno
    
with  MstrTable ( LotNo, Remainder, ROWID )
        as ( select
                LotNo
              , ( row_number() over ( order by LotNo ) + 2 ) % 3 as Remainder
              , case when ( row_number() over ( order by LotNo ) + 2 ) % 3 = 0
                     then ( row_number() over ( order by LotNo ) + 2 )
                     else case when ( row_number() over ( order by LotNo ) + 2 )
                                    % 3 = 1
                               then ( row_number() over ( order by LotNo ) + 1 )
                               else ( row_number() over ( order by LotNo ) )
                          end
                end as RowID
              from
                LotNo
           )
   select
      ( case when remainder = 0 then LotNo
             else ''
        end ) as Col1
    , ( case when remainder = 1 then LotNo
             else ''
        end ) as Col2
    , ( case when remainder = 2 then LotNo
             else ''
        end ) as Col3
    , ROWID
    from
      mstrtable
      
With MstrTable( LotNo, Remainder, ROWID)

as( 
  select
      LotNo
    , ( row_number() over ( order by LotNo ) + 2 ) % 3 as Remainder
    , case when ( row_number() over ( order by LotNo ) + 2 ) % 3 = 0
           then ( row_number() over ( order by LotNo ) + 2 )
           else case when ( row_number() over ( order by LotNo ) + 2 ) % 3 = 1
                     then ( row_number() over ( order by LotNo ) + 1 )
                     else ( row_number() over ( order by LotNo ) )
                end
      end as RowID
    from
      LotNo
  )
  
select
    ( case when remainder = 0 then LotNo
           else ''
      end ) as Col1
  , ( case when remainder = 1 then LotNo
           else ''
      end ) as Col2
  , ( case when remainder = 2 then LotNo
           else ''
      end ) as Col3
  , ROWID
  from
    mstrtable
    
 with  MstrTable ( LotNo, Remainder, ROWID )
          as ( select
                  LotNo
                , ( row_number() over ( order by LotNo ) + 2 ) % 3 as Remainder
                , case when ( row_number() over ( order by LotNo ) + 2 ) % 3 = 0
                       then ( row_number() over ( order by LotNo ) + 2 )
                       else case when ( row_number() over ( order by LotNo )
                                        + 2 ) % 3 = 1
                                 then ( row_number() over ( order by LotNo )
                                        + 1 )
                                 else ( row_number() over ( order by LotNo ) )
                            end
                  end as RowID
                from
                  LotNo
             )
    select
        max(case when remainder = 0 then LotNo
                 else ''
            end) as Col1
      , max(case when remainder = 1 then LotNo
                 else ''
            end) as Col2
      , max(case when remainder = 2 then LotNo
                 else ''
            end) as Col3
      from
        mstrtable
      group by
        ROWID