Forum Discussion

KeithSP's avatar
KeithSP
Regular Visitor
3 years ago

Sub String lookup for Multiple Values contained in a cell.

I have two queries.  One query contains a list of users with one value (Approved by) per row.  I have another query that lists more than one value in the cell (Approvers).  The values are separated by a delimiter.  I want to look up to see if the Approved By is listed in the Approver report, with a simple yes no result.  For example, if this was excel it would be IF(Approver By = "*"Approver"*", "Yes","No")

 

TIA

1 Reply

  • rbriga's avatar
    rbriga
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    Let's solve this in the query.

    Take the second query, use a function to seperate the "approved by" list into rows.

    Then inner join this table with the users table.

    In SQL, this can be:

    Now, inner join this table with the users table. 

    WITH _List AS
(
SELECT [ID], value AS [Approved By]
FROM [YourTable]
CROSS APPLY STRING_SPLIT([Approved By], ',')
),

_Valid_Approvers AS
(
SELECT
_List.ID,
COUNT(*) AS Approvers_Count
FROM _List
INNER JOIN [ApproversTable] AT
ON AT.Approver = _List.Approver
GROUP BY _List.ID
)

SELECT
[YourTable].*,
CASE
WHEN VA.Approvers_Count IS NULL THEN 'No',
ELSE 'YES'
END AS Has_Valid_Approvers
FROM [YourTable] C
LEFT JOIN _Valid_Approvers VA
ON C.[ID] = VA.[ID]