Home > Software design >  Finding values inside another rows
Finding values inside another rows

Time:11-29

I have a table with two columns. ParameterName and ParameterValue.

The easy case is when my parameter has a value like this:

enter image description here

The problem is, sometimes, a parameter value can come from another parameter. Like this:

enter image description here

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:

enter image description here

  • Related