Solved

Merge two columns with two lines

Posted on 2013-10-28
7
326 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

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

Expert Comment

by:Koen Van Wielink
Comment Utility
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 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 300 total points
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

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

8 Experts available now in Live!

Get 1:1 Help Now