SQL Server, how can I transpose data of a column [duplicate]

Question

Possible Duplicate:
Concatenate many rows into a single text string?

I have a query

SELECT city FROM cityTable

it returns

delhi
faridabad
haryana
mathura
kerla

I just want result in transpose manner something like

delhi | faridabad | haryana | mathura | kera

How is it possible?

Answer 1

SELECT STUFF (
 (SELECT N', ' + city FROM CityTable FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

Concatenate many rows into a single text string?

Answer 2

declare @city nvarchar(max)
SELECT @city = coalesce(@city+' | ', '')+city FROM cityTable 

EDIT:

In order to show the result

SELECT @city

Answer 3

You can use the PIVOT and UNPIVOT as the example posted on here

TSQL – Transpose Data using Using PIVOT and UNPIVOT

NOTE: You can find the syntax on BOL (books on line) of SQL