Declare @TransactionDetail Table (TxID int, Tx_Code1 int, Tx_Code2 int, Tx_Code3 int, Tx_Code4 int, Tx_Code5 int, Tx_Code6 int)
Insert Into @TransactionDetail (TxID, Tx_Code1, Tx_Code2, Tx_Code3, Tx_Code4, Tx_Code5, Tx_Code6)
 Select 100, 1, 2, 3, 4, 5, 6 
 Union All
 Select 200, 2, 4, 6, 8, 12, NULL 
 Union All
 Select 247, 3, 5, 7, NULL, NULL, 1  
 Union All
 Select 478, 1, NULL, 9, NULL, 5, 6 
 Union All
 Select 9832, 6, 3, NULL, 5, NULL, NULL 

 Declare @LookupCodes table (CodeNum int, CodeDesc varchar(100))
 Insert Into @LookupCodes( CodeNum, CodeDesc) 
  Select 1, 'This is Code one'
  Union All 
  Select 2, 'This is Code two'
  Union All 
  Select 3, 'This is Code three'
  Union All 
  Select 4, 'This is Code four'
  Union All 
  Select 5, 'This is Code five'
  Union All 
  Select 6, 'This is Code six'
  Union All 
  Select 7, 'This is Code seven'
  Union All 
  Select 8, 'This is Code eight'
  Union All 
  Select 9, 'This is Code nine'
  Union All 
  Select 10, 'This is Code ten'
  Union All 
  Select 11, 'This is Code eleven'
  Union All 
  Select 12, 'This is Code twelve'


-- Select * from @TransactionDetail
--Select * from @LookupCodes

 Select 
  t.TxID
  , L1.CodeDesc
  , L2.CodeDesc
  , L3.CodeDesc
  , L4.CodeDesc
  , L5.CodeDesc
  , L6.CodeDesc
 From @TransactionDetail t
 Left Outer Join @LookupCodes as L1 on t.Tx_Code1 = L1.CodeNum 
 Left Outer Join @LookupCodes as L2 on t.Tx_Code2 = L2.CodeNum 
 Left Outer Join @LookupCodes as L3 on t.Tx_Code3 = L3.CodeNum 
 Left Outer Join @LookupCodes as L4 on t.Tx_Code4 = L4.CodeNum 
 Left Outer Join @LookupCodes as L5 on t.Tx_Code5 = L5.CodeNum 
 Left Outer Join @LookupCodes as L6 on t.Tx_Code6 = L6.CodeNum 

WITH Normalized
AS
(
	SELECT T.TxID, D.Tx_Code, D.Tx_CodeNo
	FROM @TransactionDetail T
		CROSS APPLY
		(
			VALUES (TXid, Tx_Code1, 1), (TXid, Tx_Code2, 2), (TXid, Tx_Code3, 3)
				,(TXid, Tx_Code4, 4), (TXid, Tx_Code5, 5), (TXid, Tx_Code6, 6)

		) D (TxId, Tx_Code, Tx_CodeNo)
)
SELECT TxId
	,[1] AS Tx_Code1
	,[2] AS Tx_Code2
	,[3] AS Tx_Code3
	,[4] AS Tx_Code4
	,[5] AS Tx_Code5
	,[6] AS Tx_Code6
FROM
(
	SELECT N.TxId, N.Tx_CodeNo, L.CodeDesc
	FROM Normalized N
		JOIN @LookupCodes L
			ON N.Tx_Code = L.CodeNum
) AS S
PIVOT
(
	MAX(CodeDesc)
	FOR Tx_CodeNo IN ([1], [2], [3], [4], [5], [6])
) AS P;

 Select 
  t.TxID
  , MAX(CASE WHEN L.CodeNum = t.Tx_Code1 THEN L.CodeDesc END) AS CodeDesc_1
  , MAX(CASE WHEN L.CodeNum = t.Tx_Code2 THEN L.CodeDesc END) AS CodeDesc_2
  , MAX(CASE WHEN L.CodeNum = t.Tx_Code3 THEN L.CodeDesc END) AS CodeDesc_3
  , MAX(CASE WHEN L.CodeNum = t.Tx_Code4 THEN L.CodeDesc END) AS CodeDesc_4
  , MAX(CASE WHEN L.CodeNum = t.Tx_Code5 THEN L.CodeDesc END) AS CodeDesc_5
  , MAX(CASE WHEN L.CodeNum = t.Tx_Code6 THEN L.CodeDesc END) AS CodeDesc_6
 From @TransactionDetail t
 Left Outer Join @LookupCodes as L ON L.CodeNum IN ( t.Tx_Code1, t.Tx_Code2, t.Tx_Code3, t.Tx_Code4, t.Tx_Code5, t.Tx_Code6 )
 Group By
  t.TxID