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(m.name+','+t.name 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
 
 

 

https://social.msdn.microsoft.com/Forums/en-US/23be9d8c-83eb-4271-a1bc-aed514161458/convert-multiple-columns-data-in-to-single-row-with-comma-as-a-delimiter?forum=transactsql

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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