Column wise out put to row wise out put

Question

0.00/5 (No votes)

See more:

Hi sir,

The below one is the out put of my select query

CityName1	CityName2
Bangalore	Delhi
Delhi		Mumbai
Mumbai		Bangalore

I need to display it as

Bangalore - Delhi - Mumbai - Bangalore

Please help me for achieving this.

Thank you

Posted 23-Sep-14 23:01pm

kanamala subin

Updated 23-Sep-14 23:32pm

Jörgen Andersson

v2

Add a Solution

Comments

Sinisa Hajnal 24-Sep-14 5:19am

So you need single row with distinct values from two columns?

Eduard Keilholz 24-Sep-14 5:38am

I don't see the distinct, there's two Bangalore values...

Sinisa Hajnal 24-Sep-14 5:59am

Indeed, but there are no Delhi and Mumbai twice...this looks like train / bus / plane / whatever travel route

kanamala subin 24-Sep-14 5:25am

yes exactly sir

George Jonsson 24-Sep-14 5:40am

What is your current SELECT query?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2014-09-24T00:03:00

Solution 1

DECLARE @route varchar(max)

SQL

SELECT
    @route = ISNULL(@route, '') + CASE WHEN @route IS NULL THEN '' ELSE ' - ' END + 
        CityName1
FROM
    your_table

Add to that whatever you need at the end...if you describe a bit better and show your query I can make it more precise

Posted 24-Sep-14 0:03am

Sinisa Hajnal

Updated 24-Sep-14 0:04am

v2

Comments

King Fisher 24-Sep-14 6:23am

my 5+

King Fisher · Answer 2 · 2014-09-24T00:38:00

Try this:

SQL

declare @route nvarchar(max),@route1 nvarchar(max)
select @route = CityName1 from tbl_map where ID=1//here get your cityname1 top1
SELECT @route1 =STUFF((SELECT '-' + cast(CityName2 as varchar) AS [text()] FROM tbl_map  FOR XML PATH('') ), 1, 1, '' )  
select @route+'-'+@route1