Pfthb

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!

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

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)')

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

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