Creating a hierarchy in SQL Server
I have data in following format.
Table 1
e_id e_name e_type
-------------------------
1 CBC 2
2 ABC 3
3 N2 1
4 CBC1 3
5 ABC1 3
6 N1 1
Table 2
N_ID N_Name
---------------
3 N2
6 N1
Table 3
N_ID E_ID
------------
3 1
3 2
3 3
6 4
6 5
6 6
And I want to build a hierarchy as follows.
e_id e_name e_type n_id
------------------------------------
6 N1 1 6
4 - ABC1 3 6
5 - CBC1 3 6
3 N2 1 3
4 - ABC 3 3
5 - CBC 2 3
With Order by Nodes (ascending), child nodes (ascending).
I tried doing something si开发者_运维知识库milar to this,
SELECT u.e_id,
CASE WHEN e_TYPE_ID = 1 THEN u.e_name ELSE ' - ' + u.e_name END e_name,
e_TYPE_ID, su.n_ID
FROM table1 u
INNER JOIN table3 su on u.e_id = su.e_id
WHERE EXISTS (SELECT N_ID FROM table2 WHERE N_ID = CASE WHEN u.e_TYPE_ID = 1 THEN u.e_id ELSE n_ID END)
ORDER BY e_TYPE_ID, u.e_name,n_id
But I am not able to get the correct order, is there a better way to do this?
Use:
WITH summary AS (
SELECT a.e_id,
a.e_name,
a.e_type,
b.n_id,
CASE WHEN e_type = 1 THEN e_type ELSE 2 END AS rank
FROM TABLE1 a
JOIN TABLE3 b ON b.e_id = a.e_id)
SELECT s.e_id,
CASE
WHEN s.rank > 1 THEN ' - '+ s.e_name
ELSE s.e_name
END AS e_name,
s.e_type,
s.n_id
FROM summary s
ORDER BY s.n_id DESC, s.rank, s.e_name
I tested using:
WITH table2 AS (
SELECT 3 AS n_id, 'N2' AS n_name
UNION ALL
SELECT 6, 'N1'),
table1 AS (
SELECT 1 AS e_id, 'CBC' AS e_name, 2 AS e_type
UNION ALL
SELECT 2, 'ABC', 3
UNION ALL
SELECT 3, 'N2', 1
UNION ALL
SELECT 4, 'CBC1', 3
UNION ALL
SELECT 5, 'ABC1', 3
UNION ALL
SELECT 6, 'N1', 1),
table3 AS (
SELECT 3 AS n_id, 1 AS e_id
UNION ALL
SELECT 3, 2
UNION ALL
SELECT 3, 3
UNION ALL
SELECT 6, 4
UNION ALL
SELECT 6, 5
UNION ALL
SELECT 6, 6),
summary AS (
SELECT a.e_id,
a.e_name,
a.e_type,
b.n_id,
CASE WHEN e_type = 1 THEN e_type ELSE 2 END AS rank
FROM TABLE1 a
JOIN TABLE3 b ON b.e_id = a.e_id)
SELECT s.e_id,
CASE
WHEN s.rank > 1 THEN ' - '+ s.e_name
ELSE s.e_name
END AS e_name,
s.e_type,
s.n_id
FROM summary s
ORDER BY s.n_id DESC, s.rank, s.e_name
If you're using SQL Server 2008 (or 2008 R2), I would suggest using Hierarchy IDs as shown in Model Your Data Hierarchies With SQL Server 2008.
I will set you on the right path, you will need a CTE (common table expression) to accomplish this task:
;with EH as
(
select 1 level, * from Table1 t1 where e_type = 1
union all
select level+1, * from EH
join Table3 t3 on eh.e_id = t3.e_id
join Table3 t32 on t3.n_id = t32.n_id and t3.e_id
--FUUUUUUUUUUUUUUUUUUUUU!!!!!!!!!!!!!!1111
[Update]
Why do you need those spaghetti tables anyway? Why don't you keep everything in one or two tables?
精彩评论