May 9, 2015 at 12:10 pm
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'
May 9, 2015 at 5:16 pm
Based on the limited sample, it could be as simple as this:
select
case when substring(mt.colC, 1, 1) = 'X' then 'colA' when substring(mt.colC, 2, 1) = 'X' then 'colB' end as [Column]
, case when substring(mt.colC, 1, 1) = 'X' then mt.colA when substring(mt.colC, 2, 1) = 'X' then mt.colB end as Value
from [MyTable] mt;
If the real situation is more complex, then please let us know.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 9, 2015 at 9:50 pm
mister.magoo (5/9/2015)
If the real situation is more complex, then please let us know.
Hi,
Thanks for the reply.
Couple of things to note (yes, my example is rather simplified), I will try to explain.
I would rather not use a CASE statement because I fear I would be hard-coding. A lot.
You see, in the real world, colC is a string that is hundreds of characters long. There could be one or dozens of non-zero characters. I need to determine the position of each one. This position references the ordinal position(s) of the column(s) of the table (that has, respectively, the same number (hundreds) of columns). This is to get the column name.
My hope was to get the column value via the select statement based on the column name. "Dynamically". (Sort of a catch-22, it seems?) I can get the column name (via COLUMN_NAME), but I can't figure out how to put it in the SELECT other than putting the whole CTE in a variable (and then using EXEC()) with something like QUOTENAME(COLUMN_NAME) to get the value, but then that is not valid as there would be no definition of COLUMN_NAME... :crazy: ...my head is starting to hurt...
Someone else mentioned using CHOOSE() (I think?), but I have yet to try it (I'm in-between errands)...
Cheers,
Paul
May 10, 2015 at 1:06 pm
zogher (5/9/2015)
mister.magoo (5/9/2015)
If the real situation is more complex, then please let us know.
Hi,
Thanks for the reply.
Couple of things to note (yes, my example is rather simplified), I will try to explain.
I would rather not use a CASE statement because I fear I would be hard-coding. A lot.
You see, in the real world, colC is a string that is hundreds of characters long. There could be one or dozens of non-zero characters. I need to determine the position of each one. This position references the ordinal position(s) of the column(s) of the table (that has, respectively, the same number (hundreds) of columns). This is to get the column name.
My hope was to get the column value via the select statement based on the column name. "Dynamically". (Sort of a catch-22, it seems?) I can get the column name (via COLUMN_NAME), but I can't figure out how to put it in the SELECT other than putting the whole CTE in a variable (and then using EXEC()) with something like QUOTENAME(COLUMN_NAME) to get the value, but then that is not valid as there would be no definition of COLUMN_NAME... :crazy: ...my head is starting to hurt...
Someone else mentioned using CHOOSE() (I think?), but I have yet to try it (I'm in-between errands)...
Cheers,
Paul
Quick thought, start by posting a more realistic example which accurately reflects the complexity of the problem and also the full requirements. Piecemeal'ing the information is a waste of everyone's time.
😎
May 10, 2015 at 4:27 pm
Eirikur Eiriksson (5/10/2015)
zogher (5/9/2015)
mister.magoo (5/9/2015)
If the real situation is more complex, then please let us know.
Hi,
Thanks for the reply.
Couple of things to note (yes, my example is rather simplified), I will try to explain.
I would rather not use a CASE statement because I fear I would be hard-coding. A lot.
You see, in the real world, colC is a string that is hundreds of characters long. There could be one or dozens of non-zero characters. I need to determine the position of each one. This position references the ordinal position(s) of the column(s) of the table (that has, respectively, the same number (hundreds) of columns). This is to get the column name.
My hope was to get the column value via the select statement based on the column name. "Dynamically". (Sort of a catch-22, it seems?) I can get the column name (via COLUMN_NAME), but I can't figure out how to put it in the SELECT other than putting the whole CTE in a variable (and then using EXEC()) with something like QUOTENAME(COLUMN_NAME) to get the value, but then that is not valid as there would be no definition of COLUMN_NAME... :crazy: ...my head is starting to hurt...
Someone else mentioned using CHOOSE() (I think?), but I have yet to try it (I'm in-between errands)...
Cheers,
Paul
Quick thought, start by posting a more realistic example which accurately reflects the complexity of the problem and also the full requirements. Piecemeal'ing the information is a waste of everyone's time.
😎
Agreed.
Also, how about letting us know whether this design is something you have control of, because on the face of it, it seems like a pretty bad idea, but I do appreciate sometimes we have to work with what we have.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 10, 2015 at 5:05 pm
From what I understood, you don't need recursion or even a CTE. You're trying to unpivot your data to get an EVA model which might be a very bad idea to store data. You're missing how to differentiate one group of rows from another one, but you might be able to do it.
Here's a possible solution with the small information that you shared.
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');
DECLARE @DB sysname = 'Test',
@Schema sysname = 'dbo',
@Table sysname = 'myTable';
DECLARE @SQL nvarchar(max);
SELECT @SQL = 'SELECT Column_Name, Value
FROM ' + @DB + '.' + @Schema + '.' + @Table + CHAR(13) +
' CROSS APPLY( VALUES' + STUFF(( SELECT ', ('
+ QUOTENAME( cn.COLUMN_NAME, '''') + ', '
+ QUOTENAME( cn.COLUMN_NAME) + ', '
+ CAST( ORDINAL_POSITION AS varchar(3)) + ')'
FROM INFORMATION_SCHEMA.COLUMNS cn
WHERE cn.TABLE_CATALOG = @DB
AND cn.TABLE_SCHEMA = @Schema
AND cn.TABLE_NAME = @Table
FOR XML PATH(''),TYPE).value('.', 'varchar(max)'), 1, 1, '') +
')cav(Column_Name, Value, Ordinal_Position)
WHERE SUBSTRING( colC, Ordinal_Position, 1) <> ''0''
AND Column_Name <> ''colC'';';
EXECUTE sp_executesql @SQL;
GO
DROP TABLE myTable
References:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply