Merge two columns with two lines

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
MehramAsked:
Who is Participating?
 
Harish VargheseConnect With a Mentor Project LeaderCommented:
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
 
Harish VargheseProject LeaderCommented:
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
 
MehramAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Peter KipropCommented:
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
 
MehramAuthor Commented:
I am using asp.net page for the reporting and its not working on that. please help me out with some example...
0
 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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
 
Koen Van WielinkConnect With a Mentor Business Intelligence SpecialistCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.