Solved

Merge two columns with two lines

Posted on 2013-10-28
7
328 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 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

7 Experts available now in Live!

Get 1:1 Help Now