This is the backstory. While developing  an invoice form for a customer it was determined that each line item could have more than one lot number assigned to it. In fact, each line item could have many lot numbers associated with it. If these lot numbers were printed one per line a lot of paper was going to be wasted.

The solution was to place more than one lot number on a line. This article describes one solution to converting a list of lot numbers from just one per row to a list of lot numbers with three per row.

Let's start by creating a table and adding sample data

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'

Assigning a row number to each lot number is the first step in the process.

  select
      LotNo
    , row_number() over ( order by LotNo )
   from LotNo

The results:

LotNo RowNo

A     1

B     2

C     3

D     4

E     5

F     6

G     7

…

Z     26

Next the column position for each lot number has to be determined.  Looking at the result list above, A should be in Column 0, B in Column 1, C in Column 2, D in Column 0, etc.

This is accomplished using the T-SQL function MODULO.The MODULO statement returns the remainder of a division operation. The number of columns on the invoice is three, so the divisor must be three.  When the Row_Number is divided by 3, the remainder is 0, and that lot number will appear in column 1. When the Row_Number is divided by 3 and the remainder is 1, that lot number will appear in column 2. Finally when the Row_Number is divided by 3 and the remainder is 2, that lot number will appear in column 3.

Two is added to every row number.  This ensures that the Row_Number of the first three rows in the dataset when divided by 3 will equal 1.

  select
      LotNo
    , ( row_number() over ( order by LotNo ) + 2 ) % 3 as Remainder
    from
      lotno

Partial results shown:

LotNo    Remainder

A        0

B        1

C        2

D        0

E        1

With column position having been determined, the data needs to be grouped into rows. This is accomplished by using a Common Table Expression, CTE.

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

Results:

Col1  Col2  Col3  ROWID

A                 3

      B           3

            C     3 

D                 6

      E           6

            F     6

G                 9

Not exactly the desired result. But before proceeding, let’s examine each section of the CTE.

In order to produce a list of lot numbers in three columns, three pieces of information are needed. They are the lot number (LotNo), the Column number (Remainder) and the row number (RowID). These are listed in:

With MstrTable( LotNo, Remainder, ROWID)

The query definition returns these three columns from the database.

as( 

  )

RowID  bears some discussion. RowID assigns a row number to each row returned. This will be used shortly for grouping the result set into the final configuration. It utilizes a combination of the MODULO function and the Row_Number function wrapped in several CASE WHEN’s. 

The SELECT statement following the CTE uses CASE WHEN’s to determine the column position of the result set.

The final result is achieved by grouping the result set by RowID and returning the maximum value for the column

We get the desired results:

Col1       Col2       Col3

A          B          C

D          E          F

G          H          I

J          K          L

M          N          O

P          Q          R

S          T          U

V          W          X

Y          Z