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`