Transpose a word written vertically to horizontally in SQL Server

Question

I want to use T-SQL to transpose a column with a word, such as

t
r
a
i
n

into

train

I tried using pivot, but instead of getting only one row back with the transposed column, I got a 5x5 table showing 'train' along the diagonal, and NULL everywhere else. This result makes sense to me, but it's not what I want. I just want to transpose a word written vertically into the same word but written horizontally. How should I approach this with pivot? Or is there an easier way to do it otherwise?

Conversely, if I had instead started out with a word

train

how would I transpose this word to make it print vertically?

t
r
a
i
n

Thank you!

Answer 1

One way you can try to use CTE recursive

CREATE TABLE T(
   col varchar(50)
);

insert into t values ('train');

;with cte as (
  select 
         1 startIdx,
         len(col) maxlenght,
         col
  from t
  UNION ALL
  select 
         startIdx+1, 
         maxlenght,
         col
  from cte
  where startIdx + 1 <= maxlenght
)

select substring(col,startIdx,1),col
from cte

sqlfiddle

Result

t
r
a
i
n

---

If you want to let char to tranpose a column with a word, you can try to use FOR XML

create TABLE T2  (c NVARCHAR(1))
INSERT T2 VALUES('t'),('r'),('a'),('i'),('n');

SELECT STUFF((SELECT c FROM T2 FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'')

sqfiddle

RESULT

train