Solved

How do I insert a line break into a sql query

Posted on 2010-09-07
8
764 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 166 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 168 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 166 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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 look for a specific file type in a local or remote server directory using PHP.

786 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