SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT           1 ID  ,'Tom' Name    ,'I'  Status     ,'U'  Address    ,'D' Phone
INTO tab
UNION ALL SELECT 2  ,'I'    ,'D'       ,'U'      ,'D'
UNION ALL SELECT 3  ,'Pam'    ,'D'       ,'I'      ,'U'
UNION ALL SELECT 4  ,'Ken'    ,'U'       ,'U'     ,'U'
UNION ALL SELECT 5  ,'Jimmy'    ,'I'       ,'I'     ,'I'
5 rows affected

SELECT t.*
FROM tab t
CROSS APPLY (SELECT * FROM tab t2 WHERE t.id = t2.id FOR XML RAW('a')) sub(c)
WHERE sub.c LIKE '%"I"%'


ID
Name
Status
Address
Phone


1
Tom
I
U
D

2
I
D
U
D

3
Pam
D
I
U

5
Jimmy
I
I
I


Select DISTINCT t.*
From  tab t
Cross Apply (values (cast((Select t.* for XML RAW) as xml))) B(XMLData)
Cross Apply (Select 1 AS c
             From  B.XMLData.nodes('/row')  as C1(n)
             Cross Apply C1.n.nodes('./@*') as C2(a)
             Where a.value('local-name(.)','varchar(100)') not in ('id','name')
                   and a.value('.','varchar(max)')  = 'I') C;


ID
Name
Status
Address
Phone


1
Tom
I
U
D

3
Pam
D
I
U

5
Jimmy
I
I
I