declare @t table (id int, [key] varchar(20), [value] varchar(20))
insert @t
select 1, 'orderid', '100' union
select 1, 'payment', 'visa' union
select 2, 'orderid', '101' union
select 2, 'payment', 'cash' union
select 3, 'orderid', '102' union
select 3, 'payment', 'cash'

select [value] from @t a
join (select id from @t where [value] = 'cash') b 
	on a.id = b.id 
	and [value] <> 'cash'

set nocount on
Declare @Orders Table
(
	[id] int,
	[key] VarChar(10),
	[value] VarChar(10)
)

Insert into @Orders Values(1,'orderid','100')
Insert into @Orders Values(1,'Payment','Visa')
Insert into @Orders Values(2,'orderid','101')
Insert into @Orders Values(2,'Payment','cash')
Insert into @Orders Values(3,'orderid','102')
Insert into @Orders Values(3,'Payment','cash')
Insert into @Orders Values(4,'orderid','103')
Insert into @Orders Values(4,'Payment','Visa')
Insert into @Orders Values(5,'orderid','104')
Insert into @Orders Values(5,'Payment','Visa')
;

/* For Cash Orders */
Select [value] from @orders a 
Join (Select id from @Orders Where [value] IN ('cash') 
		AND isnumeric([value]) = 0) b
	On a.id = b.id
AND isnumeric([value]) = 1

/* For Credit (Non Cash) Orders */
Select [value] from @orders a 
Join (Select id from @Orders Where [value] NOT IN ('cash') 
		AND isnumeric([value]) = 0) b
	On a.id = b.id
AND isnumeric([value]) = 1

value
----------
101
102

value
----------
100
103
104