Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Convert Rows into Columns

By M Patrick Dillon,

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( 
  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

Total article views: 13706 | Views in the last 30 days: 443
 
Related Articles
ARTICLE

Union

One of the less used commands in T-SQL, the UNION command can come in very handy in a number of situ...

FORUM

SQLSERVER 2000 SQL UNION

SQL UNION

FORUM

working with union all or union

union all vs union

BLOG

Sql Server - Union and Union All

Union and Union All, both are used to select data from one or more than one tables but still they......

FORUM

How to get consecutive record number in two select statement

How to get consecutive record number in two select statement

Tags
modulo    
over    
row_number    
rows to columns    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones