Clever Geek Handbook

How to group one field in one field using SQL query

I have the following table with data

emp_no emp_name login HOD_code Level  
E43057 Ankita Barde 9:45 P00212 Direct 
E33995 Rakesh Sharma 8:15 P00212 Direct 
E31446 Amit Singh 10:45 P00212 Direct 
E44920 Shweta Salve 9:38 E33995 Indirect   
E39787 Anita Shenoy 9:15 E31446 Indirect   
E37731 Ramesh Shukla 9:10 E31446 Indirect   
E43455 Manish Shukla 11:01 E33995 Indirect   
E43130 Lubna Shaikh 9:39 E33995 Indirect

In the above table, I have one HOD code (P00212) exceeding all, so that the employee that appears in P00212 is listed as a direct employee in the LEVEL column, and all others are indirect for P00212. But these indirect employees appear in the Direct Employee list. For example, P00212 is the HOD of Rakesh Sharma (E33995) and E33995 is the HOD of 3 other employees, for example, Shweta (E44920), E43455, E43130. So I want to write my request in such a way that the output looks like this

OUTPUT:

emp_no emp_name login HOD_NO Level    
E43057 Ankita Barde 9:45 P00212 Direct

E33995 Rakesh Sharma 8:15 P00212 Direct   
E43455 Manish Shukla 11:01 E33995 Indirect 
E43130 Lubna Shaikh 9:39 E33995 Indirect 
E44920 Shweta Salve 9:38 E33995 Indirect

E31446 Amit Singh 10:45 P00212 Direct   
E39787 Anita Shenoy 9:15 E31446 Indirect 
E37731 Ramesh Shukla 9:10 E31446 Indirect

How can i do this?

+3


source to share


2 answers


SQLFiddle example



select * from t
order by CASE WHEN HOD_CODE='P00212' THEN EMP_NO ELSE HOD_CODE END,
         CASE WHEN HOD_CODE='P00212' THEN 0 ELSE 1 END
+4


source


You can try something like the following:

select 
    *, 
    case 
        when (HOD_CODE='P00212') 
        then emp_no 
        else HOD_code 
    end as Parent
from Table
Order by Parent, Emp_No


SQL Fiddle

+1


source






More articles:


All Articles