SQL Server, how can I transpose data of a column

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?

Assuming these are supposed to be columns, what should the values in the rows be? Nulls? — Thomas, May 24 '12 at 07:46
Take a look at [Pivots with dynamic columns](http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx). — Nikola Markovinović, May 24 '12 at 08:12

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

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

edited May 23 '17 at 12:29

Community

answered May 24 '12 at 07:44

Darren

1

Thanks sir, But i want just transpose of a column result. I mean, result should return in single Row and with separate column. Is it possible??? – RAKESH HOLKAR May 24 '12 at 08:09
This shows how to write a comma separated list, but what if the desired result is a table with many columns? – UnDiUdin Feb 26 '15 at 13:59

t-clausen.dk · Answer 2 · 2012-05-25T04:46:49.523

2

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

EDIT:

In order to show the result

SELECT @city

edited May 25 '12 at 04:46

answered May 24 '12 at 08:35

t-clausen.dk

It is not returning result.... – RAKESH HOLKAR May 24 '12 at 09:56
@RakeshHolkar - you have to select variable city after the operation as in my edit – t-clausen.dk May 25 '12 at 04:46

score 1 · Answer 3 · answered May 24 '12 at 08:18

1

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

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

answered May 24 '12 at 08:18

Luka Milani

3 Answers3