Transpose a word written vertically to horizontally in SQL Server

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

A simpler approach :

DECLARE 
    @t TABLE(word VARCHAR(50) ) 
INSERT INTO @t VALUES 
('t'),
('r'),
('a'),
('i'),
('n')


SELECT (SELECT word FROM @t FOR XML PATH(''), TYPE).value('.', 'VARCHAR(50)')

create table #a (word varchar(1)

insert into #a values('T'),('R'),('A'),('I'),('N')

declare @a varchar (10)=''
select @a=@a+ word  from #a
select @a

Here's a way to do the first one, especially if you need to be able to group by an account number or other columns. I also included a commented out way to do the letters as comma separated in case that's a potential need. I've used that to display all service codes on an account.

DECLARE @t TABLE (
    Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Account BIGINT NOT NULL,
    Value NVARCHAR(1) NOT NULL
);

INSERT INTO @t
(Account,Value)
VALUES
(1,'t'),(1,'r'),(1,'a'),(1,'i'),(1,'n'),
(2,'c'),(2,'a'),(2,'t');

SET NOCOUNT ON;

SELECT
    a.Account,
    REPLACE(REPLACE(v.Value,'<Value>',''),'</Value>','') AS Value
FROM (
    SELECT DISTINCT t.Account
    FROM @t AS t
) AS a
CROSS APPLY (
    SELECT v.Value
    FROM @t AS v
    WHERE v.Account = a.Account
    ORDER BY v.Id
    FOR XML PATH ('')
) AS v (Value);

/*
SELECT *
FROM (
    SELECT DISTINCT t.Account
    FROM @t AS t
) AS a
CROSS APPLY (
    SELECT
        CASE ROW_NUMBER() OVER (ORDER BY v.Id)
            WHEN 1 THEN ''
            ELSE ', '
        END + (v.Value)
    FROM @t AS v
    WHERE v.Account = a.Account
    ORDER BY v.Id
    FOR XML PATH ('')
) AS v (Value);
*/

SET NOCOUNT OFF;

There are many answers already, most of them working for this tiny case, but all of them not unproblematic:

• The quirky update has side-effects one should really deeply know about
• All XML-approaches get in troubles with escaped characters like & -> &
• All approaches might get in troubles with larger strings, when there is no explicit sort order and the engine decides to go in parallel mode

Here are two secure approaches to get single characters and two secure approaches to get the strings back. Try it out:

DECLARE @Word1 NVARCHAR(100)='train';
DECLARE @Word2 NVARCHAR(100)='this & that'; --the "&" can lead to troubles in XML

DECLARE @singleChars TABLE(ID INT IDENTITY,WordId INT,Chr CHAR(1));

--Tally (a set of running numbers)
--Best was an existing (physical) numbers table, but this shows the principles

WITH tally(nmbr) AS (SELECT TOP(LEN(@Word1)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) --the "FROM master..spt_values" is just to pick a table with a high number of rows... We do not need the values, just the set...
INSERT INTO @singleChars(WordId,Chr)
SELECT 1,SUBSTRING(@Word1,nmbr,1)
FROM tally;

--recursive
--This is a hidden RBAR approach (rather iterativ than recursive)

WITH cte AS
(
    SELECT 1 AS ChrPos,SUBSTRING(@Word2,1,1) AS Chr
    UNION ALL
    SELECT cte.ChrPos +1, SUBSTRING(@Word2,cte.ChrPos+1,1)
    FROM cte 
    WHERE cte.ChrPos+1<=LEN(@Word2)
)
INSERT INTO @singleChars(WordId,Chr)
SELECT 2,Chr
FROM cte
ORDER BY ChrPos;

--check the result

SELECT * FROM @singleChars;

--Now we re-concatenate the single characters.
--XML approach

SELECT (SELECT sc1.Chr 
        FROM @singleChars sc1
        WHERE sc.WordId=sc1.WordId
        ORDER BY ID --never forget the "ORDER BY"!
        FOR XML PATH(''),TYPE).value('.','nvarchar(100)') --read with `.value()` to ensure re-escaping
FROM @singleChars sc
GROUP BY WordId;

--STRING_AGG(needs v2017+)

SELECT STRING_AGG(sc.Chr,NULL) WITHIN GROUP(ORDER BY ID) --again the "ORDER BY"!
FROM @singleChars sc
GROUP BY WordId