?
Solved

How do I insert a line break into a sql query

Posted on 2010-09-07
8
Medium Priority
?
784 Views
Last Modified: 2013-12-13
Can someone please tell me how to insert a line break so that i can tell one order from the other in the below code. The ord_number would be the break point. Like if it pulls two rows with 77888 as the ord_number it would put an mt row before it started the rows with 89564 as the ord_number.
SELECT Distinct orderheader.ord_number, orderheader.ord_refnum, orderheader.ord_hdrnumber, stops.stp_type, stops.cmp_name, company.cmp_address1, company.cty_nmstct, stops.cmp_id, stops.stp_phonenumber, stops.stp_schdtearliest, stops.stp_reftype, stops.stp_refnum, stops.stp_weight, stops.stp_weightunit, stops.stp_count, stops.stp_countunit
FROM orderheader, stops, company WHERE orderheader.ord_hdrnumber = stops.ord_hdrnumber AND orderheader.ord_status='AVL' AND company.cmp_id = stops.cmp_id ORDER BY orderheader.ord_hdrnumber, stops.stp_schdtearliest ASC

Open in new window

0
Comment
Question by:GD_GRAY
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 13

Assisted Solution

by:khairil
khairil earned 664 total points
ID: 33621262
Hi,

you can use this.. add char(13) at the end of the selection, OR you can use <br>
if you like to view it in web page. However it is not the right format to do this. Why not just let the application front end do this job.

===================================
SELECT Distinct orderheader.ord_number, orderheader.ord_refnum, orderheader.ord_hdrnumber, stops.stp_type, stops.cmp_name, company.cmp_address1, company.cty_nmstct, stops.cmp_id, stops.stp_phonenumber, stops.stp_schdtearliest, stops.stp_reftype, stops.stp_refnum, stops.stp_weight, stops.stp_weightunit, stops.stp_count, stops.stp_countunit, char(13) as linebreak
FROM orderheader, stops, company WHERE orderheader.ord_hdrnumber = stops.ord_hdrnumber AND orderheader.ord_status='AVL' AND company.cmp_id = stops.cmp_id ORDER BY orderheader.ord_hdrnumber, stops.stp_schdtearliest ASC
==================================
Goood luck.

0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33621297
Char(13) should work.. it would be better if you can post the expected result set for more help

Example For you ref:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26426288.html



0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33621318
Adding CHAR(13) would insert a line break at the specified location.
Can you give more detailed information with some sample result set along with expected result set to help you better..
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:GD_GRAY
ID: 33621674
Sure and thank you...
What I would like is to have a space or mt line separating each set of order numbers like the image.
Query3.jpg
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 672 total points
ID: 33621805
You will need to do an inner join and then using and then compare the order no with Order no in the next row.. use a case statement to add char(13) to your last column if the value in the next row is different from the present row value
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 664 total points
ID: 33623864
The kind of formatting as mentioned in the snapshot can't be done using SQL Server or T-SQL and it is recommended or can be easily done from the front end code or application..
0
 
LVL 13

Expert Comment

by:khairil
ID: 33623979
Hi,

I'm sorry but truly I cannot see idea on having line break inside Excel like rows and columns.

But is you still insist then you should try using database Export Data command (right click on selected database in SQL Management Console, choose Export Data). Select local as your data source and Excel as your output, then instead of  copying data from table choose write specific query. Paste or write back you query and then continue until finish exporting.

Good luck.
0
 

Author Closing Comment

by:GD_GRAY
ID: 33648950
I cant get this working but you all seem to have an idea, I just cant do it. But thank you anyway.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

840 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