Solved

Merge two columns with two lines

Posted on 2013-10-28
7
332 Views
Last Modified: 2013-10-28
Dear Experts,
I have two columns Name & FatherName. Date we can show like this:

Select Name +'-'+ FatherName from table
result

Iqbal - Israfeel

My requirement is show data in two line (in first line name and second line fathername) like this in same column.

Iqbal
Israfeel

Please help.
Rgds.
Iqbal
0
Comment
Question by:Mehram
[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
7 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39604900
Hello Iqbal,

You need to insert a new line character in between firstname and lastname, like below:

select Name + char(13) + char(10) + FatherName from table

But this will not show up in the SQL server management studio, if you are viewing the results in Grid mode. It will show two lines in text mode. This will also work if you are using some front end applications like .Net.

-Harish
0
 

Author Comment

by:Mehram
ID: 39604915
Sir,
I need to display it through application in report. the report run sp to get detail. Would it work? sometime need to run directly on sql query to get data un-format form and past in excel.. what will happen in that case?
0
 
LVL 6

Expert Comment

by:Peter Kiprop
ID: 39604941
Mehran,

 You will be required to do some grouping to give you the desired results. e.g group by Name and Fathername. If you are using Crystal reports group by the two fields and supress the header and detail section and then show the results in the group footers and this will work well.

You do not need to do a special query to achieve this.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Mehram
ID: 39604948
I am using asp.net page for the reporting and its not working on that. please help me out with some example...
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39604975
Dear Mehram,

Do you have some sample data available? I'm assuming that there is more information in your table than just the first and the last name, perhaps a unique ID for the individual? If so we can use that to create a temp table which holds the final data set you need.
Please provide some example and/or the table layout you are using. Primary key information would be helpful too.
0
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 300 total points
ID: 39604983
Actualy assuming you have this unique ID in your table a temp table isn't even required:

select		person_ID
		,	name
		,	'FirstName' as 'NameType'
from	yourTable

union all

select		person_ID
		,	FatherName
		,	'LastName' as 'NameType'
from	 yourTable

order by 1, 3

Open in new window


This will first take the unique ID and the first name, then add the same unique ID and the last name. By sorting that final result on column 1 you'd get the first and the last name for each individual. The "NameType" column is required to make sure it will always sort the first name first, then the last name.
Hope this helps.
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 200 total points
ID: 39604999
Hello Iqbal,

"I need to display it through application in report. the report run sp to get detail. Would it work?"
SP will return the data properly in two lines (a New Line character in between the words makes it two lines), but it is up to the display control to decide how to display. What control are you using in ASP.NET to display the results?

"sometime need to run directly on sql query to get data un-format form and past in excel.. what will happen in that case?"
It will not work with new line characters.

-Harish
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

717 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