Joins - 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

General SQL Server Forums

New to SQL Server Programming

Joins

Author

Topic

cool.mugil
Starting Member

32 Posts

Posted - 2010-07-09 : 01:48:53

Hi,
I have a table structure like this below,

EmpID Name Mgr1 Mgr2 Mgr3 Mgr4
1 A 2 3 4 5
2 B 6 7 8 NULL
3 C 8 9 NULL NULL
4 D 10 NULL NULL NULL
5 E NULL NULL NULL NULL
6 F NULL NULL NULL NULL
7 G NULL NULL NULL NULL
8 H NULL NULL NULL NULL
9 I NULL NULL NULL NULL
10 J NULL NULL NULL NULL

I want the output as follows
EmpID Name Mgr1 Mgr2 Mgr3 Mgr4
1 A B C D E

Please note that some of the mgr fields may be null.

how to write query for this.I am using sql server 2005.

Thanks in advance.
Mugil

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-09 : 02:22:22

Hello,

You can try this:
select
EmpID,
Name,
(select Name from <your_table_name> where empid=t.mgr1) as mgr1,
(select Name from <your_table_name> where empid=t.mgr2) as mgr2,
(select Name from <your_table_name> where empid=t.mgr3) as mgr3,
(select Name from <your_table_name> where empid=t.mgr4) as mgr4
from
<your_table_name> t

Best regards,

Devart Team

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-09 : 06:30:52

if you concern is to not viewing a data with not value on mgr
you may condition to it as not null


Select * from tbl 
where 
mgr1 is not null and 
mgr2 is not null and 
mgr3 is not null and 
mgr4 is not null

A maze make you much more better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources