Creation of View with rules

Transact-SQL
1

Hi Expert
Can someone please guide me how to achieve below

For transaction with reference ID, the coupon and type is to follow the existing coupon and type of the transaction. For example C23458 and C345679 has coupon of 2.1 and type of 'A'

The objective is to query out those transaction and put into a view that of the same reference ID and populate the coupon and type of the same reference ID using the existing coupon and type of the same reference ID.

2

Not sure I follow. You said

but they are in fact blank in your sample.

Could you please explain a bit more?

3

Base on the table, C23458 and C345679 and CI12347 has the same reference ID.
CI12347 has a coupon and type of 2.1 and A. As such my query will have to populate coupon and type of C23458 and C345679 to be 2.1 and A

4

Thanks for your help

5 
select a.Transaction,  b.Coupon, b.Type, a.[Reference ID]
from [Table 1] a
cross apply
(
     select max(b.Coupon) Coupon, max(b.Type) Type
    from [Table1] b
    where a.[Reference ID] = b.[Reference ID]
) b