-- 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
----------------------------------------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden