Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Merge two columns with two lines

Posted on 2013-10-28
7
Medium Priority
?
335 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 900 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 600 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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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