Solved

How do I insert a line break into a sql query

Posted on 2010-09-07
8
759 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:GD_GRAY
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 16

Accepted Solution

by:
vdr1620 earned 168 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

12 Experts available now in Live!

Get 1:1 Help Now