Convert Columns To Tree - 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 2012 Forums

Transact-SQL (2012)

Convert Columns To Tree

Author

Topic

samir.first
Starting Member

34 Posts

Posted - 2014-05-21 : 02:03:31

I have Table with 3 columns


col1         col2           col3     
A            samir          L
A            samir          Z
A            Ali            E
C            Jon            B
C            Beter          T
..... etc

I need To convert This Table To Tree


ID        Col1           ParentID
1         A              NULL
2         Samir          1
3         L              2
4         Z              2
5         Ali            1
6         E              5
7         C              Null
8         Jon            7
9         Beter          7

Samir

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 03:25:56

[code]declare @tbl table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)

insert into @tbl (col1, col2, col3)
select 'A', 'samir', 'L' union all
select 'A', 'samir', 'Z' union all
select 'A', 'Ali', 'E' union all
select 'C', 'Jon', 'B' union all
select 'C', 'Beter', 'T'

; with cte as
(
select ID = row_number() over (order by coalesce(parent, col), case when parent is null then 1 else 2 end),
col, parent
from
(
select col = col1, parent = NULL
from @tbl
group by col1
union all
select col = col2, parent = col1
from @tbl
group by col2, col1
union all
select col = col3, parent = col2
from @tbl
group by col3, col2
) c
)
select c1.ID, c1.col, ParentID = c2.ID
from cte c1
left join cte c2 on c1.parent = c2.col[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources