Column Data to a Row with Recursive

Besides the For XML path way, we can use recursive CTE to concatenate column data within a group to a single data point.


if object_id('test','U') is not null
drop table test 

create table test 
	(Item_hierarchy_id int identity,
 	 parent_item_hierarchy_id int null, 
	 name varchar(10) not null)

insert into test (parent_item_hierarchy_id, name) values
(null,	'item1'), (1, 'child1'), (1, 'child2'), 
(null, 'item2'), (4, 'child3'), (4, 'child4'), (4, 'child5'),(4, 'child6')

;with mycte as (
Select Item_hierarchy_id, Item_hierarchy_id parent_item_hierarchy_id,Cast(name  as varchar(1000)) name,  0 as lvl 
from test where parent_item_hierarchy_id is null
Union all
Select t.Item_hierarchy_id, m.parent_item_hierarchy_id, Cast(',' as varchar(1000)) name ,lvl+1 as lvl 
from test t join  mycte m  
on t.parent_item_hierarchy_id  = m.parent_item_hierarchy_id 
and t.Item_hierarchy_id > m.Item_hierarchy_id  ) 

,mycte1 as (
select name ,row_number()Over(Partition by parent_item_hierarchy_id Order by lvl desc) rn  from mycte


Select name from mycte1 where rn=1

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s