Solved

How do I insert a line break into a sql query

Posted on 2010-09-07
8
767 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

830 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