 # Convert Rows into Columns

,

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
GO
CREATE TABLE [dbo].[LotNo](
[IDNo] [bigint] IDENTITY(1,1) NOT NULL,
[LotNo] [varchar](50) NULL
) ON [PRIMARY]
GO
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(
```
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

)

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.

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

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

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

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`

## Rate

4.16 (58)

You rated this post out of 5. Change rating

## Rate

4.16 (58)

You rated this post out of 5. Change rating