Merge 2 Column Values insert New Line between 2 column value

Question

Post reply

Merge 2 Column Values insert New Line between 2 column value

Meha

Right there with Babe

Points: 772
More actions
January 21, 2010 at 2:08 am

#215934

Hi All,
I want to merge 2 columns between Newline/Carriage return.
For Example..
My table
EmpID | First Name | Last Name
------------------------------
1 | First | Second
2 | chris | cintrella
...
i want Output for the following format...
EmpID | Name
--------------
1 | First
Second
2 | chris
cintrella
...
Please give solution....
warm regards,
Ms.Hema

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

dave.repton SSCrazy Points: 2468 More actions · Answer 1

If using reporting services this would be possible by using a Matrix / tablix and applying grouping

Meha Right there with Babe Points: 772 More actions · Answer 2

Thanks for your Reply.

But I want to get from query O/P

then I need to fill in grid using .NET....

dave.repton SSCrazy Points: 2468 More actions · Answer 3

dave.repton

SSCrazy

Points: 2468

January 21, 2010 at 4:49 am

#1106601

howabout the unpivot operator ?

Meha Right there with Babe Points: 772 More actions · Answer 4

I used the following query...

Select 'chris'+ char(13) + 'cintrella' as Name

But Its work in query result as text.

plz. help me

dave.repton SSCrazy Points: 2468 More actions · Answer 5

another way would be as shown below...

declare @table table (EmpID int, FirstName varchar(45), LastName varchar(45))

insert @table values(1, 'First', 'Second')

insert @table values(2, 'Chris', 'Cintrella')

select empid

,'FirstName' NameType

,FirstName

from @table

union all

select empid

,'LastName' NameType

,LastName

from @table

order by empid, nametype

Meha Right there with Babe Points: 772 More actions · Answer 6

I used the following query...

Select 'chris'+ char(13)| + 'cintrella' as Name

But Its work in query result as text.

Meha Right there with Babe Points: 772 More actions · Answer 7

Thanks repton,

But I already tried this way.

But I need a single row with New Line in grid

|-----------------------------|

| 1 |Chris |

| Cintrella |

-------------------------------

| 2 |Latha |

| Sen |

-------------------------------

Please give me a solution...

dave.repton SSCrazy Points: 2468 More actions · Answer 8

As far as I am aware SQL will not be able to provide the dataset in the way you require - you would need to handle the display of the resultset within your app...

Could be wrong though - other may be able to advise

LutzM SSC Guru Points: 107049 More actions · Answer 9

dave.repton (1/21/2010)
As far as I am aware SQL will not be able to provide the dataset in the way you require - you would need to handle the display of the resultset within your app...
Could be wrong though - other may be able to advise

Dave, you are right and wrong at the same time...

Right in terms of your recommendation to have the app dealing with string formatting like this but wrong in terms of SQL capabilities (example below using the sample setup you used).

Even though it's possible to do it using SQL I'd like to know the reason for this kind of data "structure"... @hemasenthu: would you mind sharing the business case?

SELECT cast(EmpID AS varchar(10)) +'|' + FirstName + char(13) + LastName AS 'EmpID | Name'

FROM @table

/* result:

EmpID | Name

----------------

1|First

Second

2|Chris

Cintrella

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Meha Right there with Babe Points: 772 More actions · Answer 10

Thanks for your Reply ...

I want the result for above format..

because I need the 2 columns are show in a single column but the 2nd column value is under first column value...

Please give me a solution.

Meha Right there with Babe Points: 772 More actions · Answer 11

Hi all,

Thanks for ur reply.

But i want following format O/P:

Because I want to First column value is in top & 2nd column value is followed by Ist column value on second line. Both Column values are in Single Column.

Please Give me a solution....

I'm awaiting for ur Reply...

Meha Right there with Babe Points: 772 More actions · Answer 12

Hi all,

Thanks for ur reply.

But i want following format O/P:

EmpNoFirstNameLastName

1Kumar Sen

2Roy John

EmpNoName

1Kumar

Sen

2Roy

John

Because I want to First column value is in top & 2nd column value is followed by Ist column value on second line. Both Column values are in Single Column.

Please Give me a solution....

I'm awaiting for ur Reply...

dave.repton SSCrazy Points: 2468 More actions · Answer 13

Excellent Lutz - learn something new everyday...

not thought of putting the results to text

declare @table table (EmpID int, FirstName varchar(45), LastName varchar(45))

insert @table values(1, 'First', 'Second')

insert @table values(2, 'Chris', 'Cintrella')

SELECT cast(EmpID AS varchar(10)) +'|' + FirstName + char(13) + LastName AS 'EmpID | Name'

FROM @table

Meha - the above from Lutz when set to display the result in text will work for you. If you want this as seperate rows I don't know how you would achieve this without including extra columns - again, I'd be looking for the apps to handle this I think.

Meha Right there with Babe Points: 772 More actions · Answer 14

But I want to show the 2nd column value in next line

Otherwise i want to show the differentiate 1st column & 2nd column value in single column..

Is it possible in .NET Grid...

Please give me a solution...