Multi-Column Report for Serial Numbers

I recently had to create a report that needed adjacent columns of serial numbers much like columns in a Word document. I pored over several advices but in the end discretely defining the number of serial numbers per column worked out best.
I used the code below to get a row number and list them and then created separate data sets in the sub-report which was inserted in the body of the main report.
image
SQL Code for Data Sets
select s1.Num as R1, s1.SERIAL_71 as S1
from
(
SELECT top 100 percent SERIAL_71, ordnum_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @Order)
order by SERIAL_71
) AS S1
where s1.Num <= 10
select s1.Num as R2, s1.SERIAL_71 as S2
from
(
SELECT top 100 percent SERIAL_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @Order)
order by SERIAL_71
) AS S1
where s1.Num >= 11 and s1.Num <= 20
select s1.Num as R3, s1.SERIAL_71 as S3
from
(
SELECT top 100 percent SERIAL_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @OrdNum)
order by SERIAL_71
) AS S1
where s1.Num >= 21 and s1.Num <= 30

Leave a Reply

Your email address will not be published. Required fields are marked *