You can prevent duplication of service codes by leveraging an INSTEAD OF INSERT, UPDATE trigger where each service column is conditionally populated depending on whether a preceeding service column has the same code. For example:

CREATE TABLE MyInvoice

(

Invoice char(6) not null primary key

, Service1 char(6) null

, Service2 char(6) null

, Service3 char(6) null

, Service4 char(6) null

, Service5 char(6) null

);

GO

CREATE TRIGGER MyInvoiceTrigger on MyInvoice

INSTEAD OF INSERT

AS

BEGIN

INSERT INTO MyInvoice SELECT

Invoice

, Service1

, case when Service2

not in (isnull(Service1,''))

then Service2 else null end

, case when Service3

not in (isnull(Service1,''),isnull(Service2,''))

then Service3 else null end

, case when Service4

not in (isnull(Service1,''),isnull(Service2,''),isnull(Service3,''))

then Service4 else null end

, case when Service5

not in (isnull(Service1,''),isnull(Service2,''),isnull(Service3,''),isnull(Service4,''))

then Service5 else null end

FROM inserted;

END;

GO

insert into MyInvoice values ('001','A','B',null,null,null);

insert into MyInvoice values ('002','A','B','A','C',null);

insert into MyInvoice values ('003','A','B',null,'C','A');

select * from MyInvoice;

Invoice Service1 Service2 Service3 Service4 Service5

001 A B NULL NULL NULL

002 A B NULL C NULL

003 A B NULL C NULL