Populating rows to columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Populating rows to columns

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-17 : 22:11:26

expense_estimate_info


emp_id    	exp_no  living_exp_desc  	living_exp_amount  	misc_exp_desc  	misc_exp_amount
123		1	AUTO-INS			100		FOOD1		12
123		2	RENT				1200		WAL		35
123		3	ELEC				30		RAL		25
123		4	WATER				25		MOV		16

The above shown table is my source (expense_estimate_info) and the destination table is expense_info
where the data from the source should be populated as shown.The exp_no 5 in the destination would be
misc_exp details of the exp_no 1 of the source and is the same with the rest of exp_nos and the types would be
living_exp would be 1 and misc_exp details are 2's.Please help me with the query

expense_info


emp_id  exp_no  	exp_type	exp_desc	exp_amount
123	1		1		AUTO-INS	100
123	2		1		RENT		1200		
123	3		1		ELEC		30		
123	4		1		WATER		25		
123	5		2		FOOD1		12
123	6		2		WAL		35						
123	7		2		RAL		25
123	8		2		MOV		16

TestEngineer
Starting Member

29 Posts

Posted - 2005-12-18 : 09:03:24

You could do two nested SELECT Statements on the same table and then do a "UNION ALL" between them.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-18 : 13:41:17

Is this query the best optimized one ...

insert into expense_info
(
emp_id,exp_no,exp_type,exp_desc,exp_amount)

Select emp_id,exp_no,1,living_exp_desc,living_exp_amount
from expense_estimate_info where emp_id not exist in
(select emp_id from expense_info)
union all
Select emp_id,exp_no+4,2,misc_exp_desc,misc_exp_amount
from expense_estimate_info where emp_id not exist in
(select emp_id from expense_info)

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-18 : 16:53:27

Could please somebody verify my query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 02:16:53

See if this returns your expected result

Select emp_id ,exp_no ,living_exp_desc ,living_exp_amount ,misc_exp_desc
union all
Select emp_id,exp_no ,living_exp_desc,living_exp_amount,misc_exp_amount

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-19 : 02:38:04

[code]insert into expense_info
(
emp_id,exp_no,exp_type,exp_desc,exp_amount)

Select emp_id,exp_no,1,living_exp_desc,living_exp_amount
from expense_estimate_info where emp_id not exist in
(select emp_id from expense_info)
union all
Select emp_id,exp_no+4,2,misc_exp_desc,misc_exp_amount
from expense_estimate_info where emp_id not exist in
(select emp_id from expense_info)[/code]
What is expense_info's pk ? You might have a problem here. Post your table structure.

-----------------
[KH]
Learn something new everyday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources