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 2008 Forums
 Transact-SQL (2008)
 How to get Hierarchical reporting query in sql

Author  Topic 

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2015-02-13 : 03:24:50
How to get Hierarchical reporting query in sql server?

I have a solution oracle and given for your reference
SQL>
SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );

Table created.

SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
last_name AS employee
FROM employee
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;

but I don't know in sql server

if anyone knows, tell me

Thanks in advance.


V.NAGARAJAN

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-13 : 06:28:44
Hi,

It is better to post some raw data and the expected output. (i don't know much about Oracle)

so , i created an example, maybe will guide you.


;WITH EMP
AS
(SELECT 1 AS employee_id , NULL AS manager_id , 'Manag First' AS first_name , 'LastName ' AS last_name UNION ALL
SELECT 2 , 1 , 'Emp 2 F', 'Emp2 Last'UNION ALL
SELECT 3 , 2 , '3 First', 'Last'UNION ALL
SELECT 4 , 2 , '4 First', 'Last'UNION ALL
SELECT 5 , 3 , '5 First', 'LastName'UNION ALL
SELECT 6 , 3 , '6 First', 'Last')



,cte_Emp
AS
(
SELECT employee_id ,NULL AS manager_ID, CAST('' AS VARCHAR(20)) AS Name , 0 as LVL
FROM EMP
WHERE manager_id IS NULL

UNION ALL

SELECT E.employee_id , E.manager_id, CAST(C.name +'/'+ RiGHT(REPLACE(STR(E.manager_id),' ','0') ,3) AS VARCHAR(20)), LVL+ 1
FROM cte_Emp AS c
INNER JOIN Emp AS E
ON E.manager_id = c.employee_id
)




SELECT * FROM cte_Emp


and the output:

employee_id manager_ID Name LVL
1 NULL 0
2 1 /001 1
3 2 /001/002 2
4 2 /001/002 2
5 3 /001/002/003 3
6 3 /001/002/003 3





sabinWeb MCP
Go to Top of Page

lomew01
Starting Member

4 Posts

Posted - 2015-02-23 : 06:46:18
Hey Guys well i think that a hierarchical query is a type of SQL query that manages hierarchical design data. They are unique case of more common recursive fixpoint concerns which compute transitive closures.Thanks!!

unspammed
Go to Top of Page
   

- Advertisement -