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'   

Table (Development_phase) is:
phase_type_code		phase_name	Phase_Desc
1				Others		Others
2				newAphase	descr
3				TestPhase	TestPhaseDescription
4				Deploy1		Deploy1
5				Operate1	Operate1
7				ab			ab

Table3 (language_path) is: 
Language_Path_code	Language_path_name	Language_Path_desc
1				Others				Others
2				Mac OS				Mac OS
3				MicroSoft			Wast tool
4				Database			ETIL
5				BASIC				BASIC

Output should be:

 172,	'phase_type_code',	newAphase,	Operate1,	'Jan 17 2013  7:22PM',	'chandu' 
 172,	'phase_type_code',	Operate1,	ab,		'Jan 17 2013  7:25PM',	'chandu'
 172,	'Language_path_code',	MicroSoft,	Others,	        'Jan 17 2013  7:25PM',	'chandu' 
 172,	'Language_path_code',	Others,		MicroSoft,	'Jan 17 2013  7:41PM',	'chandu'   
  

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