sql server desired output from tables

Question

                                         
        ID     name       Dept       Manager       
        101    Mukesh     SW               
        102    Ram        SW         101       
        103    sham       SW         101       
        104    rahul      SW         101       
        105    Rajat      HQ               
        106    akhilesh   HQ         105         
        107    sachin     HQ         105       

I've this table and I want output like below

                                  
        dep    Manager    name          
        SW     Mukesh     Ram       
                          Sham          
                          Rahul         
        HQ     Rajat      akhilesh          


                                    

Answer 1

Kindly find below query as you want. i created MyTest as table name which you need to replace.

SELECT 
    Case WHEN ISNULL(SecondTable.Id,0) = 0
         THEN FirstTable.Dept
         ELSE ''
    END As Department, 

    Case WHEN ISNULL(SecondTable.Id,0) = 0
         THEN Manager.Name 
         ELSE ''
    END  As Manager, 
    FirstTable.Name FROM MyTest As FirstTable
LEFT JOIN MyTest As SecondTable ON
    SecondTable.ID = (SELECT Top 1  MyTest.ID
                      FROM MyTest 
                      WHERE MyTest.Manager = FirstTable.Manager
                        AND MyTest.dept = FirstTable.dept
                        AND MyTest.Id < FirstTable.Id
                      ORDER BY MyTest.ID Desc) 
LEFT JOIN MyTest As Manager On Manager.Id = FirstTable.Manager  WHERE FirstTable.Manager <> 0 ORDER BY FirstTable.dept, FirstTable.ID

Answer 2

What you are looking for is to retrieve the hierachical data, with sql 2008 you can use common table expression. check out this link Recursive Queries Using Common Table Expressions the data output is flat (it will repeat SW and Mukesh for each row). Hope it helps

Answer 3

That is not possible in sql only. What you can do is get the dep and manager name for each of them.

SELECT t1.Dept AS DEP, t2.name AS MANAGER, t1.name AS NAME
FROM table1 t1
INNER JOIN table1 t2 ON t1.manager = t2.id

sqlfiddle demo

This will return something like:

DEP MANAGER NAME
SW  Mukesh  Ram
SW  Mukesh  sham
SW  Mukesh  rahul
HQ  Rajat   akhilesh
HQ  Rajat   sachin

Then in your programming language of choice you would do that formatting.