SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get this result?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/18/2013 :  07:39:25  Show Profile  Reply with Quote

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'   
  


--
Chandu

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/18/2013 :  08:03:38  Show Profile  Reply with Quote
Would this work for you? I took the liberty of creating the remaining test data for you.
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'
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/18/2013 :  08:34:21  Show Profile  Reply with Quote
Is there any another approach for above problem?
In my case, there are 9 tables ( for joining). I think this is somewhat lengthy......

--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/18/2013 :  08:38:30  Show Profile  Reply with Quote
If all the tables have the same structure, you can UNION all of them and join to the unioned result
;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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/18/2013 :  09:34:54  Show Profile  Reply with Quote
Ok thank you. If you know the UNPIVOT methos for this task.. Let me know please

--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/18/2013 :  13:13:59  Show Profile  Reply with Quote
Not sure, Chandu. Don't you use UNPIVOT when you want to convert the data into a collection of columns into data in a single column? I don't see how that applies here.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/21/2013 :  06:06:59  Show Profile  Reply with Quote
2nd solution working fine James...

Thank You

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000