Use Jeff Moden's splitter function (copied below for convenience).

/* Sample Data */

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[Type] CHAR(1) NULL,

[TypeDesc] CHAR(1) NULL,

PRIMARY KEY (ID),

UNIQUE (ID))

INSERT INTO #TempTable

SELECT 'T','A'

UNION

SELECT 'P','B'

UNION

SELECT 'M','C'

UNION

SELECT 'B','D'

UNION

SELECT 'X','X'

UNION

SELECT 'Y','Y'

UNION

SELECT 'Z','Z'

/* Display sample data */

SELECT

[Type]

,[TypeDesc]

FROM

#TempTable

/* Show just the rows that match the delimited string */

DECLARE @strTypes VARCHAR(50)

SET @strTypes = 'T,P,M,B'

SELECT DISTINCT

[Type]

,[TypeDesc]

FROM

#TempTable AS tt

CROSS APPLY

dbo.tvfDelimitedSplit8K(@strTypes,',') AS ds

WHERE

tt.[Type] = ds.Item

CREATE FUNCTION [dbo].[tvfDelimitedSplit8K]

(

@pString VARCHAR(8000)

,@pDelimiter CHAR(1)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

WITH E1(N)

AS (

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

), --10E+1 or 10 rows

E2(N)

AS (

SELECT

1

FROM

E1 a

,E1 b

), --10E+2 or 100 rows

E4(N)

AS (

SELECT

1

FROM

E2 a

,E2 b

), --10E+4 or 10,000 rows max

cteTally(N)

AS (

SELECT TOP (ISNULL(DATALENGTH(@pString),0))

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM E4

),

cteStart(N1)

AS (

SELECT

1

UNION ALL

SELECT

t.N + 1

FROM

cteTally t

WHERE

SUBSTRING(@pString,t.N,1) = @pDelimiter

),

cteLen(N1,L1)

AS (

SELECT

s.N1

,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)

FROM

cteStart s

)

SELECT

ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)

,Item = SUBSTRING(@pString,l.N1,l.L1)

FROM

cteLen l ;

GO