I once had a similar requirement and the best option was to generate the columns in the reporting tool. That was on VFP and I'm not sure if it's possible to do it in every reporting tool available, but it would certainly be easier to maintain and understand (single column from recordset printed in several columns on the report).

However, if someone needs to do it in T-SQL, it's a good option, but certainly the CASE statement is not necessary.

WITH MstrTable AS(

SELECT LotNo

, ( ROW_NUMBER() OVER ( ORDER BY LotNo ) + 2 ) % 3 as Remainder

, ( ROW_NUMBER() OVER ( ORDER BY LotNo ) + 2 ) / 3 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;

--Second option just for fun, but shouldn't be better and might be worse.

WITH Groups AS(

SELECT LotNo

, NTILE( (SELECT COUNT(*) FROM LotNo) / 3 + 1) OVER(ORDER BY LotNo) as RowID

FROM LotNo

),

mstrtable AS(

SELECT LotNo,

RowID,

ROW_NUMBER() OVER( PARTITION BY RowID ORDER BY LotNo) AS remainder

FROM Groups

)

SELECT

MAX ( CASE WHEN remainder = 1 THEN LotNo ELSE '' END ) as Col1

,MAX ( CASE WHEN remainder = 2 THEN LotNo ELSE '' END ) as Col2

,MAX ( CASE WHEN remainder = 3 THEN LotNo ELSE '' END ) as Col3

FROM mstrtable

GROUP BY ROWID;