x
Solved

SQL Query return rows horizontally

Posted on 2013-01-16
Medium Priority
465 Views
I was requested to modify a query/report to save paper.
The query looks up a series of serial numbers associated with an order and then lists them on a packing slip.

Right now the list shows every serial number individually per line. I want to be able to list  5 serial numbers per line before going to the next one.

Sample Table Data
SEQ_NUM      |       SERIAL_NUMBER      |      Order_Num
1                              A1234                                  SO-123
2                              A3253                                  SO-123
3                              A5764                                  SO-123
4                              A7654                                  SO-123
5                              A0045                                  SO-123

A1234
A3253
A5764
A7654
A0045

I would prefer this:
A1234       A3253      A5764     A7654     A0045

Our product is grouped in boxes of 5 items.
So, line 1-5 would be in the first box, 6-10 in the next......... The sequence restarts for every order.

Any suggestions? Thanks.
0
Question by:mossmis
• 5
• 5
• 4
• +2

LVL 10

Expert Comment

ID: 38784701
SQL Server is not particularly good at presentation layer type stuff but this works (assuming that seq_num is a steadily increasing integer):

select
serial_number + ' ' +
(select serial_number from seq_table where seq_num = outside.seq_num+1) + ' ' +
(select serial_number from seq_table where seq_num = outside.seq_num+2) + ' ' +
(select serial_number from seq_table where seq_num = outside.seq_num+3) + ' ' +
(select serial_number from seq_table where seq_num = outside.seq_num+4)
from seq_table as outside
where seq_num % 5 = 1
0

LVL 41

Accepted Solution

ID: 38784711
try this

select order_num, [1], [2], [3], [4], [5]
from (
select seq_num, serial_number, order_num, row_number() over (partition by case when seq_num % 5 = 0 then 1 else 0 end order by seq_num) rn
from yourtable
) o
pivot (max(serial_number) for rn in ([1], [2], [3], [4], [5])) p
0

LVL 10

Expert Comment

ID: 38784714
My previous answer fell over with nulls - here is a better version:

select
coalesce(serial_number,'') + ' ' +
coalesce((select serial_number from seq_table where seq_num = outside.seq_num+1),'') + ' ' +
coalesce((select serial_number from seq_table where seq_num = outside.seq_num+2),'') + ' ' +
coalesce((select serial_number from seq_table where seq_num = outside.seq_num+3),'') + ' ' +
coalesce((select serial_number from seq_table where seq_num = outside.seq_num+4),'')
from seq_table as outside
where seq_num % 5 = 1
0

LVL 3

Expert Comment

ID: 38784726
0

Author Comment

ID: 38784829

Ralmada, Your query returns me 500 lines all all NULLS in the 1-5 columns.

The order I picked had an even 500 lines. I was hoping to get 100 back in the query. The seq_num is of type integer and goes sequentially and is consistent through all orders. Serial_number is of type varchar(15).

contactnaeem, the link looks like a start, however I'm not sure how to apply the 5 serial_number per line as every order varies in quantity.
0

LVL 10

Expert Comment

ID: 38784844
My query works against your sample data (although I made up an extra 5rows to make sure I got two lines).

Are you able to post soem DDL for sample data?
0

LVL 3

Expert Comment

ID: 38784856
mossmis,

Actually you need a label report. Not sure which reporting tool you are using but in crystal reports this can be done very easily using label reports and you can have limit of 5 labels per row. You can use your normal query in that report.
0

LVL 10

Expert Comment

ID: 38784870
Here is the DDL I used and tested against:

create table seq_table(seq_num int, serial_number char(5), Order_num char(5))

insert into seq_table(seq_num,serial_number)
values(1,'A1234')
,(2,'A3253')
,(3,'A5764')
,(4,'A7654')
,(5,'A0045')
,(6,'B4321')
,(7,'B7891')
,(8,NULL)
,(9,'B1234')
,(10,'B9823')

(chose to ignore Order_num for now...)
0

LVL 39

Expert Comment

ID: 38785371
try this

select  Order_Num,  BoxNo, [1], [2], [3], [4], [5]
from (
select (seq_num-1)/5 BoxNo, serial_number,  Order_Num,
row_number() over (partition by (seq_num-1)/5 order by seq_num) rn
from yourtableName) main
pivot (max(serial_number) for rn in ([1], [2], [3], [4], [5])) p
0

LVL 41

Expert Comment

ID: 38785437
Sorry I meant to use NTILE function

select order_num, [1], [2], [3], [4], [5]
from (
select seq_num, serial_number, order_num, NTILE(5) over (partition by order_num order by seq_num) rn
from yourtable
) o
pivot (max(serial_number) for rn in ([1], [2], [3], [4], [5])) p
0

Author Comment

ID: 38787187
My appologies, I gave it another spin because the new examples also return nulls. I had to enter a where clause for all of the samples posted here. My table has 3.6million+ rows in it. The report only looks up one order at a time. I originally put it here:

select  Order_Num,  BoxNo, [1], [2], [3], [4], [5]
from (
select (seq_num-1)/5 BoxNo, serial_number,  Order_Num,
row_number() over (partition by (seq_num-1)/5 order by seq_num) rn
from yourtableName) main
pivot (max(serial_number) for rn in ([1], [2], [3], [4], [5])) p
where Order_Num = 'SO-123'

But it actually needed to be here:

select  Order_Num,  BoxNo, [1], [2], [3], [4], [5]
from (
select (seq_num-1)/5 BoxNo, serial_number,  Order_Num,
row_number() over (partition by (seq_num-1)/5 order by seq_num) rn
from yourtableName) main
where Order_Num = 'SO-123'
pivot (max(serial_number) for rn in ([1], [2], [3], [4], [5])) p
0

Author Comment

ID: 38787400
I also just realized that some of the orders have multiple SKUs also that I didn't mention earlier as  I didn't think it would be a problem. This is what the whole table actucally has:

SEQ_NUM,
SKU,
SERIAL_NUMBER,
ORDER_NUM,
CREATED_DATE

Here is a DDL with sample data

CREATE TABLE [SHIPMENT_LIST] (
[SEQ_NUM] int NULL,
[SKU] varchar(15) NULL,
[SERIAL_NUMBER] varchar(15) NULL,
[ORDER_NUM] varchar(15) NULL,
[CREATED_DATE] datetime NULL)

insert into SHIPMENT_LIST (seq_num,serial_number, SKU, ORDER_NUM, CREATED_DATE)
values(1, 'A1234', '444', 'SO-123', '12/12/2012'),
(2, 'A8546', '444', 'SO-123', '12/12/2012'),
(3, 'A4123', '444', 'SO-123', '12/12/2012'),
(4, 'A1122', '444', 'SO-123', '12/12/2012'),
(5, 'A8455', '444', 'SO-123', '12/12/2012'),
(1, 'B0012', '555', 'SO-123', '12/12/2012'),
(2, 'B0014', '555', 'SO-123', '12/12/2012'),
(3, 'B1547', '555', 'SO-123', '12/12/2012'),
(4, 'B5474', '555', 'SO-123', '12/12/2012'),
(5, 'B9874', '555', 'SO-123', '12/12/2012'),
(1, 'G4144', '888', 'SO-123', '12/12/2012'),
(2, 'G8999', '888', 'SO-123', '12/12/2012')

So now I realized I need to group those lines by SKU as the SEQ_NUM resets for every new SKU within that order. Is there a group by I need to enter to accomplish this?
0

LVL 41

Expert Comment

ID: 38787924
Have you tried my suggestion with the NTILE function?

1) You should put the WHERE inside not outside

>>the report look up one order at a time<<
2) then change the partition by to be SKU instead order_num

Check the updated version:

``````select order_num, SKU, [1], [2], [3], [4], [5]
from (
select seq_num, serial_number, order_num, SKU, NTILE(5) over (partition by SKU order by seq_num) rn
from yourtable
where Order_Num = 'SO-123'

) o
pivot (max(serial_number) for rn in ([1], [2], [3], [4], [5])) p
``````
0

LVL 41

Expert Comment

ID: 38787986
Now per your example it looks like you only have five rows per SKU, is that correct? In that case you can just do a simple pivot

select order_num, SKU, [1], [2], [3], [4], [5]
from (
select seq_num, serial_number, order_num, SKU
from yourtable
where Order_Num = 'SO-123'

) o
pivot (max(serial_number) for seq_num in ([1], [2], [3], [4], [5])) p
0

Author Comment

ID: 38788203

There could be mutiple rows per SKU. The simple pivot only returns one row per sku and leaves out the rest.

Your ntile function returns all lines of the order and one serial number per line in varying positions.
0

LVL 41

Expert Comment

ID: 38788472
>>Your ntile function returns all lines of the order and one serial number per line in varying positions.<<

Is that relevant?

You could also try

select  Order_Num,  SKU, [1], [2], [3], [4], [5]
from (

select row_number() over (partition by (sqnum-1)/5 order by sqnum) rn, SKU, serial_number,  Order_Num,
from (
select *, row_number() over (order by SKU) sqnum
from yourtableName
where Order_Num = 'SO-123'
) a
) main
pivot (max(serial_number) for rn in ([1], [2], [3], [4], [5])) p
0

Author Closing Comment

ID: 38886538
Although I couldn't get exactly what I wanted, pivots seem to be the way to go. My data is as consistent as I would like to accomplish this. But I think Ralmada got me on the right track. Thank you
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.