Agfdhyk

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