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)
 Help reqd - on hierarchy query

Author  Topic 

koushikchandra
Starting Member

24 Posts

Posted - 2011-05-06 : 04:51:12
Hi,

I have a dataset like below :

Cust ParentCust
1 2
2 3
3 -1
4 2
5 4

i.e. 3 is the top customer. 2 is reporting to 3. 1 & 4 reporting to 2. 5 is reporting to 4.

Here in this example it is a 3 level hierarchy. It can be till 10th level as well.

Can you please provide some sample SQL using which I can get the output like below :

lvl0 lvl1 lvl2 lvl3
3 2 1
3 2 4 5

Regards,
Koushik

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-06 : 08:01:02
Code below is not tested enough to my satisfaction, but it does work for your example. If you find problems, please post the details?
CREATE TABLE #tmp (Cust INT, ParentCust INT)
INSERT INTO #tmp VALUES (1,2),(2,3),(3,-1),(4,2),(5,4);

WITH cte (C, p) AS
(
SELECT cast(Cust AS VARCHAR(MAX)), Cust
FROM #tmp WHERE ParentCust = -1
UNION ALL
SELECT CAST(t.Cust AS VARCHAR(MAX)) + '|' + C,
t.Cust
FROM #tmp t INNER JOIN cte c ON c.p = t.ParentCust
)
SELECT REPLACE(REVERSE(c1.c),'|',' ') FROM cte c1 WHERE
NOT EXISTS (SELECT * FROM CTE c2 WHERE c2.c LIKE '%'+c1.C AND c1.c <> c2.c)
Go to Top of Page

koushikchandra
Starting Member

24 Posts

Posted - 2011-05-06 : 08:55:51
Hi,

Thanks for the reply.
But I am actually looking for these values in different columns. Here the result is coming in one single column.

Regards,
Koushik
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-06 : 11:00:25
May be this? I did this only for four levels; if you have more levels, they can be added in a similar way.

CREATE TABLE #tmp (Cust INT, ParentCust INT)
INSERT INTO #tmp VALUES (1,2),(2,3),(3,-1),(4,2),(5,4);


WITH cte (c1,c2,c3,c4,cust,lvl,ParentCust, s) AS
(
SELECT
Cust,NULL,NULL,NULL,
cust,
1,
ParentCust,
cast(Cust AS VARCHAR(MAX))
FROM
#tmp
WHERE
ParentCust = -1
UNION ALL
SELECT
c.c1,
CASE WHEN lvl = 1 THEN t.Cust ELSE c.c2 END,
CASE WHEN lvl = 2 THEN t.Cust else c.c3 END,
CASE WHEN lvl = 3 THEN t.Cust else c.c4 END,
t.Cust,
lvl+1,
t.ParentCust,
CAST(t.Cust AS VARCHAR(MAX)) + '|' + s
FROM
#tmp t INNER JOIN cte c ON c.Cust = t.ParentCust
)
SELECT c1,c2,c3,c4 FROM cte ct1
WHERE NOT EXISTS (SELECT * FROM CTE ct2 WHERE ct2.s LIKE '%'+ct1.s AND ct1.s <> ct2.s);

drop table #tmp;
Go to Top of Page
   

- Advertisement -