Sub String lookup for Multiple Values contained in a cell.

[KeithSP]

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

[rbriga]

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]