gid         gleaderid          itemid      grank 
46768	        23014	      36501	      	1
46769	        23014	      35405	        2
46771	        23014	      44787		4
46772	        23014	      30026		5
50127	        23014	      18537		3
222140	        23014	      27258		6
222141	        23014	      47619		7

id        title
23014     Canon 430EX II Kit
18537	SLR System Camera Case
27258	10 Free Prints
30026	Lens Cleaning Kit
35405	Tripod
36501	430EX II Flash
44787	Batteries & Rapid Charger
47619	Digital Photo Magazine

Select i.id,
'description' = 'Kit contains' + childitem1.title + childitem2.title + etc
from imitem i
inner JOIN groups ON groups.gLeaderID = I.ID AND groups.grank = 1
left JOIN groups groups2 ON groups2.gLeaderID = I.ID AND groups2.grank = 2
left JOIN groups groups3 ON groups3.gLeaderID = I.ID AND groups3.grank = 3
left JOIN groups groups4 ON groups4.gLeaderID = I.ID AND groups4.grank = 4
left JOIN groups groups5 ON groups5.gLeaderID = I.ID AND groups5.grank = 5
left JOIN groups groups6 ON groups6.gLeaderID = I.ID AND groups6.grank = 6
left JOIN groups groups7 ON groups7.gLeaderID = I.ID AND groups7.grank = 7


inner JOIN IMItem childItem1 ON childItem1.id = groups.itemid 
left JOIN IMItem childItem2 ON childItem2.id = groups2.itemid 
left JOIN IMItem childItem3 ON childItem3.id = groups3.itemid 
left JOIN IMItem childItem4 ON childItem4.id = groups4.itemid 
left JOIN IMItem childItem5 ON childItem5.id = groups5.itemid 
left JOIN IMItem childItem6 ON childItem6.id = groups6.itemid 
left JOIN IMItem childItem7 ON childItem7.id = groups7.itemid 

id	description
23014	Kit contains  Canon Speedlite 430EX II Flash & Tripod & Camera Case & Batteries & Rapid Charger &  Lens Cleaning Kit & Free Prints

;with groups (gid,gleaderid,itemid,grank )
as
(
SELECT 46768,23014,36501,1 UNION ALL
SELECT 46769,23014,35405,2 UNION ALL
SELECT 46771,23014,44787,4 UNION ALL
SELECT 46772,23014,30026,5 UNION ALL
SELECT 50127,23014,18537,3 UNION ALL
SELECT 222140,23014,27258,6 UNION ALL
SELECT 222141,23014,47619,7
),items(id,title)
as
(
SELECT 23014,'Canon 430EX II Kit' UNION ALL
SELECT 18537,'SLR System Camera Case' UNION ALL
SELECT 27258,'10 Free Prints' UNION ALL
SELECT 30026,'Lens Cleaning Kit' UNION ALL
SELECT 35405,'Tripod' UNION ALL
SELECT 36501,'430EX II Flash' UNION ALL
SELECT 44787,'Batteries & Rapid Charger' UNION ALL
SELECT 47619,'Digital Photo Magazine' 
)
select id,'Kit contains '+ i_outer.title + REPLACE(titles,',','&') as description
from items as i_outer
outer apply (
	select ' , '+i_inner.title
	from groups as g_inner
	join items as i_inner
	on i_inner.id = g_inner.itemid
	where g_inner.gleaderid = i_outer.id
	order by g_inner.grank
	for xml path('')
	) as ex(titles)
where id = 23014