Hi,
I am a junior programmer that has just figured out CTE with recursion, but still struggling with a problem.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Help?
Cheers,
Paul
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
FROM myTable
UNION ALL
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) + pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '')
FROM cte
WHERE colC > ''
AND PATINDEX('%[^0]%', colC) > 0
)
SELECT cn.COLUMN_NAME
FROM cte
INNER JOIN INFORMATION_SCHEMA.COLUMNS cn
ON cte.pos = cn.ORDINAL_POSITION
WHERE cn.TABLE_CATALOG = 'myTableCatalog'
AND cn.TABLE_SCHEMA = 'dbo'
AND cn.TABLE_NAME = 'myTable'