Setting values to a single row result

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)

Setting values to a single row result

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-09-15 : 00:10:22

I have a table tbl_emp_details and I want to do something like this

emp_id   action_flag  action_code
100	  25            0
100	  50            1
100       75            1
100       91            0
100       15            0

I can get my result using the below query but I want to get it in a single row


Select emp_id,case
		 when action_code = 0 then 'Y'
		 else
		     'N'
              end as emp_attorney_flag
              
Where emp_id=100 and action_flag=25

Select emp_id,case
		 when action_code = 0 then 'Y'
		 else
		     'N'
              end as emp_citizen_flag
              
Where emp_id=100 and action_flag=50

Select emp_id,case
		 when action_code = 0 then 'Y'
		 else
		     'N'
              end as emp_contract_flag
              
Where emp_id=100 and action_flag=75

I want to have result like

emp_id  emp_attorney_flag emp_citizen_flag  emp_contract_flag
100		N		Y		Y

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-15 : 00:15:49

this is cross-tab / pivot table


select emp_id,
       max(case when action_flag = 25 then 
                     case when action_code = 0 then 'Y' else 'N' end
                end) as emp_attorney_flag,
       max(case when action_flag = 50 then 
                     case when action_code = 0 then 'Y' else 'N' end
                end) as emp_citizen_flag,
       max(case when action_flag = 75 then 
                     case when action_code = 0 then 'Y' else 'N' end
                end) as emp_contract_flag
from   tbl_emp_details
group by emp_id

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-09-15 : 02:34:17

Thanks a lot...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources