Solved

SQL Query return rows horizontally

Posted on 2013-01-16
17
455 Views
Last Modified: 2013-02-13
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
Comment
Question by:mossmis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +2
17 Comments
 
LVL 10

Expert Comment

by:Matt Bowler
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

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

by:Matt Bowler
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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 3

Expert Comment

by:contactnaeem
ID: 38784726
0
 

Author Comment

by:mossmis
ID: 38784829
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 10

Expert Comment

by:Matt Bowler
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

by:contactnaeem
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

by:Matt Bowler
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

by:appari
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

by:ralmada
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

by:mossmis
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

by:mossmis
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

by:ralmada
ID: 38787924
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
 
LVL 41

Expert Comment

by:ralmada
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

by:mossmis
ID: 38788203
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
 
LVL 41

Expert Comment

by:ralmada
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

by:mossmis
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

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

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

Join & Ask a Question