how to do this? help please - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

how to do this? help please

Author

Topic

stovan
Starting Member

9 Posts

Posted - 2009-10-30 : 05:51:38

hello

i using sql 2005.

i have following table :

1> structure table which look like this
hierachy_level son father
01 01000001 0
10 01000002 01000001
10 01000088 01000001
10 01000167 01000001
10 01000928 01000001
10 01001357 01000001
10 01001460 01000001
10 01001589 01000001
10 01001804 01000001
20 01000003 01000002
20 01000043 01000002
20 01000057 01000002

2> my second table is just desciption
it give example
ref desc
01000001 MANAGER
01000002 OFFICE OF THE MANAGER
01000003 GEOGRAPHICAL INFORMATION AND POLICY
01000004 PENSIONS
01000005 INSURANCE
01000006 EXECUTIVE
01000007 EXECUTIVE-ELECTRICITY
01000008 MONTHLY

my desired output should be like

a
a
a b
a b c e
a b c e
a b d
a b d

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-30 : 06:00:11

how many level do you have ? Is there a maximum ? What if you have 100 level, are you going to show in 100 columns ?

Check out the Books Online on Recursive CTE

KH
[spoiler]Time is always against us[/spoiler]

stovan
Starting Member

9 Posts

Posted - 2009-10-30 : 06:08:31

i tried using the father to son logic but no luck

let me try explain simpler

i have employee and this employee belong to a branch and that branch belong to a unit and unit to dept and dept to cluster

cluster
cluster unit dept
cluster unit dept

if looking from top level - some employee dont have dept (management)
and filters down like that

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-30 : 06:10:57

refer to the Books Online on the recursive cte

http://msdn.microsoft.com/en-us/library/ms186243.aspx

KH
[spoiler]Time is always against us[/spoiler]

stovan
Starting Member

9 Posts

Posted - 2009-10-30 : 06:57:07

Thank you.

This is helping

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources