Solved

SQL Query return rows horizontally

Posted on 2013-01-16
17
444 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
  • 5
  • 5
  • 4
  • +2
17 Comments
 
LVL 9

Expert Comment

by:MattSQL
Comment Utility
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
Comment Utility
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

by:MattSQL
Comment Utility
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

by:contactnaeem
Comment Utility
0
 

Author Comment

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

by:MattSQL
Comment Utility
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
Comment Utility
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

by:MattSQL
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 39

Expert Comment

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now