Quick addition of another key element (SRC_ID)

😎

USE tempdb;

GO

;WITH BASE_DATE(BD_ID,SRC_ID,BD_FLAG) AS

( SELECT BD_ID,SRC_ID,BD_FLAG FROM (VALUES

(1 ,1,'ABC' )

,(2 ,1,'123' )

,(3 ,1,'DEF' )

,(4 ,2,'ABC' )

,(5 ,2,'123' )

,(6 ,2,'DEF' )

,(7 ,3,'ABC' )

,(8 ,3,'123' )

,(9 ,3,'DEF' )

,(10,4,'ABC' )

,(11,4,'123' )

,(12,4,'DEF' )

) AS X(BD_ID,SRC_ID,BD_FLAG)

)

,TRANSLATE_SET(SRC_ID,V_BEFORE,V_AFTER) AS

(SELECT * FROM (VALUES

(1,'ABC','XYZ' )

,(1,'123','PDQ' )

,(1,'DEF','BLAH' )

,(2,'ABC','Fighters' )

,(2,'123','CAT' )

,(2,'DEF','Blue' )

,(3,'ABC','Strange' )

,(3,'123','Strange')

,(3,'DEF','Tea' )

,(4,'ABC','Phoenix' )

,(4,'123','Indeed' )

,(4,'DEF','Stuff' )

) AS X(SRC_ID,V_BEFORE,V_AFTER)

)

SELECT

BD.BD_ID

,BD.SRC_ID

,BD.BD_FLAG

,'BECOMES' AS TR_ACTION

,TS.V_AFTER

FROM BASE_DATE BD

INNER JOIN TRANSLATE_SET TS

ON BD.BD_FLAG = TS.V_BEFORE

WHERE BD.SRC_ID = TS.SRC_ID ;

Results

BD_ID SRC_ID BD_FLAG TR_ACTION V_AFTER

----------- ----------- ------- --------- --------

1 1 ABC BECOMES XYZ

2 1 123 BECOMES PDQ

3 1 DEF BECOMES BLAH

4 2 ABC BECOMES Fighters

5 2 123 BECOMES CAT

6 2 DEF BECOMES Blue

7 3 ABC BECOMES Strange

8 3 123 BECOMES Strange

9 3 DEF BECOMES Tea

10 4 ABC BECOMES Phoenix

11 4 123 BECOMES Indeed

12 4 DEF BECOMES Stuff