DECLARE @t TABLE( wm_software_id	int, FieldName	varchar(100), PreVal varchar(100),	CurrVal	varchar(100), log_ts Datetime,	last_change_userid varchar(100))
INSERT INTO @t
SELECT 172,	'phase_type_code',	2,	5,	'Jan 17 2013  7:22PM',	'chandu' union all
SELECT 172,	'phase_type_code',	5,	7,	'Jan 17 2013  7:25PM',	'chandu' union all    
SELECT 172,	'Language_path_code',	3,	1,	'Jan 17 2013  7:25PM',	'chandu' union all
SELECT 172,	'Language_path_code',	1,	3,	'Jan 17 2013  7:41PM',	'chandu'   

DECLARE @t2 TABLE (phase_type_code INT, phase_name VARCHAR(100), phase_Desc VARCHAR(100));
insert into @t2 values ('1','Others','Others');
insert into @t2 values ('2','newAphase','descr');
insert into @t2 values ('3','TestPhase','TestPhaseDescription');
insert into @t2 values ('4','Deploy1','Deploy1');
insert into @t2 values ('5','Operate1','Operate1');
insert into @t2 values ('7','ab','ab');

DECLARE @t3 TABLE (Language_Path_code INT,	Language_path_name VARCHAR(100),	Language_Path_desc VARCHAR(100))
insert into @t3 values ('1','Others','Others');
insert into @t3 values ('2','Mac OS','Mac OS');
insert into @t3 values ('3','MicroSoft','Wast tool');
insert into @t3 values ('4','Database','ETIL');
insert into @t3 values ('5','BASIC','BASIC');

SELECT
	t.wm_software_id,
	t.FieldName	,
	COALESCE(t2.phase_name,t4.Language_path_name) AS col3,
	COALESCE(t3.phase_name,t5.Language_path_name) AS col4,
	t.log_ts,
	t.last_change_userid
FROM
	@t t
	LEFT JOIN @t2 t2 ON t2.phase_type_code= t.PreVal AND t.FieldName = 'phase_type_code'
	LEFT JOIN @t2 t3 ON t3.phase_type_code = t.CurrVal AND t.FieldName = 'phase_type_code'
	LEFT JOIN @t3 t4 ON t4.Language_Path_code = t.PreVal AND t.FieldName = 'Language_path_code'
	LEFT JOIN @t3 t5 ON t5.Language_Path_code = t.CurrVal AND t.FieldName = 'Language_path_code'

;WITH cte AS
(
	SELECT *,'phase_type_code' AS FieldName  FROM @t2
	UNION ALL
	SELECT *,'Language_path_code' AS FieldName  FROM @t3
)
SELECT
	t.wm_software_id,
	t.FieldName	,
	t2.phase_name AS col3,
	t3.phase_name AS col4,
	t.log_ts,
	t.last_change_userid
FROM
	@t t
	LEFT JOIN cte t2 ON t2.phase_type_code= t.PreVal AND t.FieldName = t2.FieldName
	LEFT JOIN cte t3 ON t3.phase_type_code = t.CurrVal AND t.FieldName = t3.FieldName