• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

SQL Query return rows horizontally

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
mossmis
Asked:
mossmis
  • 5
  • 5
  • 4
  • +2
1 Solution
 
Matt BowlerDB team leadCommented:
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
 
ralmadaCommented:
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
 
Matt BowlerDB team leadCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
contactnaeemCommented:
0
 
mossmisAuthor Commented:
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
 
Matt BowlerDB team leadCommented:
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
 
contactnaeemCommented:
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
 
Matt BowlerDB team leadCommented:
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
 
appariCommented:
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
 
ralmadaCommented:
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
 
mossmisAuthor Commented:
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
 
mossmisAuthor Commented:
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
 
ralmadaCommented:
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 

Open in new window

0
 
ralmadaCommented:
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
 
mossmisAuthor Commented:
ralmada,

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
 
ralmadaCommented:
>>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
 
mossmisAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now