I have a table with two columns. ParameterName and ParameterValue.
The easy case is when my parameter has a value like this:
The problem is, sometimes, a parameter value can come from another parameter. Like this:
This situation may get more complicated and the second parameter also gets its value from the third parameter and so on...
I know it might be a common case and might have an easy solution but I couldn't find the answer and I don't know what is the name of this type of situation.
Can anyone help me? I need to bring the value for all parameters. I thought the answer was recursive cte but after trying it, it seems it is not the answer.
I put the code for my sample table below:
DECLARE @T TABLE
( ParameterName NVARCHAR(128) NULL,
ParameterValue NVARCHAR(128) NULL
)
INSERT @T
VALUES ( '$A', 'SOME VALUE'),
( '$B', '$A')
SELECT * FROM @T
CodePudding user response:
The answer was recursive CTE and worked like this:
I also added more data to my table.
DECLARE @T TABLE
( ParameterName NVARCHAR(128) NULL,
ParameterValue NVARCHAR(128) NULL
)
INSERT @T
VALUES ( '$A', 'SOME VALUE'),
( '$B', '$A'),
( '$C', 'AAAAA'),
( '$D', '$A'),
( '$E', '$D')
;WITH VALS
AS ( SELECT ParameterName, ParameterValue
FROM @T
WHERE ParameterValue NOT LIKE '$%'
UNION ALL
SELECT T.ParameterName, V.ParameterValue
FROM @T AS T
INNER JOIN VALS AS V ON T.ParameterValue = V.ParameterName
)
SELECT * FROM VALS
Now it works like this: