ChrisM@Work (7/24/2014)

---

-- make a table with two rows, two columns

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))

INSERT INTO @t(data) SELECT 'Jacob'

INSERT INTO @t(data) SELECT 'Sebastian'

SELECT * FROM @t

GO

----------------------------------------------------------------------------------------------------

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))

INSERT INTO @t(data) SELECT 'Jacob'

INSERT INTO @t(data) SELECT 'Sebastian'

-- a CTE which provides n rows containing values 1 through n,

-- where n is the longest word in column [data]

SELECT DISTINCT Number

FROM master..spt_values

WHERE Number BETWEEN 1 AND (SELECT MAX(LEN(data))FROM @t)

GO

----------------------------------------------------------------------------------------------------

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))

INSERT INTO @t(data) SELECT 'Jacob'

INSERT INTO @t(data) SELECT 'Sebastian'

-- pick the letters from [data] one by one, putting each one on a separate row of the output

;WITH Numbers(N) AS (

SELECT DISTINCT Number

FROM master..spt_values

WHERE Number BETWEEN 1 AND (SELECT MAX(LEN(data))FROM @t)

)

SELECT T2.id, N,

SUBSTRING(data,N,1)

FROM @t T2 JOIN Numbers ON N<=LEN(data)

--WHERE T1.id=T2.id

ORDER BY T2.id, N DESC -- added T2.id for clarity

--FOR XML PATH('')

GO

----------------------------------------------------------------------------------------------------

-- finally, FOR XML PATH concatenates the values from each row (for each id)

-- ORDER BY N DESC ensures that the letters are concatenated in the reverse order from collection.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))

INSERT INTO @t(data) SELECT 'Jacob'

INSERT INTO @t(data) SELECT 'Sebastian'

;WITH Numbers(N) AS (

SELECT DISTINCT Number

FROM master..spt_values

WHERE Number BETWEEN 1 AND (SELECT MAX(LEN(data))FROM @t)

)

SELECT

id,

data_reversed

FROM @t T1

CROSS APPLY(

SELECT

SUBSTRING(data,N,1)

FROM @t T2 JOIN Numbers ON N<=LEN(data)

WHERE T1.id=T2.id

ORDER BY N DESC

FOR XML PATH('')

)x(data_reversed)

ORDER BY id DESC

----------------------------------------------------------------------------------------------------