Solved

# SQL Query return rows horizontally

Posted on 2013-01-16
444 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

ie.  Instead of reporting  this
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 9

Expert Comment

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

ralmada earned 500 total points
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 9

Expert Comment

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

0

Author Comment

MattSQL, Your Query returns nothing

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 9

Expert Comment

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

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 9

Expert Comment

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

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

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

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

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

Have you tried my suggestion with the NTILE function?

To your other questions:

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

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

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

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

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Introduction SQL Server Integration Services can read XML files, thatâ€™s known by every BI developer. Â (If you didnâ€™t, donâ€™t worry, Iâ€™m aiming this article at newcomers as well.) But how far can you go? Â When does the XML Source component become â€¦
SQL Server engine let youÂ use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties â€¦
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

#### 728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!