Q:

SQL to select row in target table based on data in multiple rows in driver table

All,
Please see below data values in two tables:
FIRST Table: or Driver table contains Filter Criteria for selecting IDs from second Table
Key1    Value
1    Banks
1    Col1|Small
2    InsuranceCompany
2    Col2|Global
3    Banks
3    Col1|Big
3    Col2|Local
4    CreditUnion

Script
GO
CREATE TABLE [dbo].[TEST_DRIVER](
    [Key1] [int] NOT NULL,
    [Value] [varchar](50) NOT NULL
    );
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('1', 'Banks');
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('1', 'Col1|Small');
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('2', 'InsuranceCompany');
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('2', 'Col2|Global');
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('3', 'Banks');
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('3', 'Col1|Big');
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('3', 'Col2|Local');
GO
INSERT INTO dbo.TEST_DRIVER(Key1, Value)
VALUES('4', 'CreditUnion');
GO

Note: 
a) Filter Criteria may exist in 1, 2 or 3 rows.
b) First selection criteria will always join with InstitutionType column of second table, however second and third criteria may or may not exist and the column to which it will join is specified in data itself with | separating columns name with value
SECOND Table: IDs from this table need to be found based on filter criteria in FIRST Table
ID  InstitutionType     Col1        Col2
100 Banks               Small   
200 Banks                           Global
300 Banks               Big         Local
400 InsuranceCompany    Small       Local
500 InsuranceCompany                Global
600 CreditUnion         Small       Local
700 CreditUnion                     Global
800 CDO                             Global

Script
CREATE TABLE [dbo].[TEST_TARGET](
    [ID] [int] NOT NULL,
    [InstitutionType] [varchar](50) NOT NULL,
    [Col1] [varchar](50) NULL,
    [Col2] [varchar](50) NULL
    );
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('100',  'Banks', 'Small', '');
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('200',  'Banks', '', 'Global');
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('300',  'Banks', 'Big', 'Local');
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('400',  'InsuranceCompany', 'Small', 'Local');
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('500',  'InsuranceCompany', '', 'Global');
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('600',  'CreditUnion', 'Small', 'Local');
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('700',  'CreditUnion', '', 'Global');
GO
INSERT INTO [dbo].[TEST_TARGET](ID, InstitutionType, Col1, Col2)
VALUES('800',  'CDO', '', 'Global');
GO

EXPECTED OUTPUT: 
ID
100
300
500
600
700

I am able to do it using cursor/while loop however I want to do it using query logic. Can someone please try to answer this interesting problem?

A:

The code below will work and produce the exact results as your sample output, but seems a bit off how you are matching the records.  Looks like you are only matching on Value and ignoring col1 & col2 in the driver table if they are both not available (which is why you are selecting 600 & 700) but not if one or the other exist?  I would expect that once the driver table is transformed with col1 and col2 in the proper format, you would want to just join to it on all the columns like this:
on t.InstitutionType = d.Value
and coalesce(t.col1,'') = coalesce(d.col1,'')
and coalesce(t.col2,'') = coalesce(d.col2,'')
If there happened to be a mistake in your sample output, you can update the join condition in the code below.
with test_driver_cte AS 
(select 
Key1,
max(case when Value not like '%|%' then Value end) Value,
max(case when Value like '%|%' and col = 'Col1' then value2 end) Col1,
max(case when Value like '%|%' and col = 'Col2' then value2 end) Col2
from (
    select 
        key1,
        Value,
        case when value like '%|%'
             then substring(Value, 1, 4)
        end col,
        case when value like '%|%'
             then substring(Value, 6, 10)  --change 2nd parameter to length of Value-6
        end value2                   
    from test_driver
    ) td
group by key1
)

select distinct ID 
from test_target t
join test_driver_cte d
on (t.InstitutionType = d.Value and d.col1 is null and d.col2 is null)
or (t.InstitutionType = d.Value and t.col1 = d.col1)
or (t.InstitutionType = d.Value and t.col2 = d.col2)

