SELECT on column name from query's result set in same query?

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'

What do you want to return for the "column value" when there is more than 1 row in the table?

Hi,

I don't think that can happen, my usage of the cte (should) only allow(s) one row.

Also, just FYI, I'm not allowed to create/utilize functions or temp tables...

Cheers,
Paul

I'm curious what the reason for this is - referring to columns by ordinal position is frowned on (in "Best Practices" ), as there is a significant risk of "things changing in the future".

It sounds like ("I'm not allowed to create/utilize functions or temp tables") as though this is an educational exercise, rather than real world?? (as an employer I'd prefer Colleges teach Real World) !!)

In case it helps when I approach Dynamic SQL I do something like this:

DECLARE @strSQL nvarchar(MAX)
SELECT @strSQL = 'SELECT Col1, Col2 '
       + 'FROM ' + @MyTable + ' '
       + 'WHERE SomeColumn = ' + @SomeValue

that can be executed with EXEC (@strSQL) or, better, using sp_ExecuteSQL

sp_ExecuteSQL takes parameters, and avoids the issue that concatenating strings might muck up the SQL if they have embedded quotes etc., plus string concatenation of non-char data needs CASTing etc.

So change the above slightly to

       + 'WHERE SomeColumn = @SomeValue'

and then do

	EXEC sp_ExecuteSQL @strSQL,
		N'@SomeValue varchar(999),
		@SomeValue = @SomeValue

key benefit of this is that SQL will cache the query plan based on the exact value of @strSQL, and if that always says "WHERE SomeColumn = @SomeValue", rather than "WHERE SomeColumn = 'foo'" and "WHERE SomeColumn = 'bar'", then it will be the same for each possible value of @MyTable. It also avoids CASTing and embedded quotes (and the whole issue of SQL Injection).

Next problem is debugging the darn thing! Folk often output the @strSQL string, before the execute, using PRINT or similar and then try running the code which is actually generated to find where it is bust. Correct the code sample, and then correct the code that generated it.

We also put the @strSQL into a logging table because the other issue (once it generates valid, executable, code) is to optimise it. For that purpose we want to see what combinations of things users actually wind up generating. This is particularly import where the dynamic SQL adds specific phrases to the WHERE clause, depending on what choices the user makes (on an APPs Crtieria Form or similar)

1 Like