?
Solved

How do I insert a line break into a sql query

Posted on 2010-09-07
8
Medium Priority
?
773 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
[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
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

770 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