求树级json数据的组成语句

[missukiss02]

        YJGHDLMC        RJGHDLMC        SJGHDLMC

	建设用地	交通水利用地	铁路用地
	建设用地	其他建设用地	风景名胜设施用地
	建设用地	其他建设用地	特殊用地
	农用地	        城乡建设用地	其他独立
	农用地	        耕地	        旱地
	农用地	        耕地	        水浇地

有这样的一个表。。我要查出一个字符串,这样：(其实就是一个数级的json的数据格式)
{"YJGHDLMC":"建设用地","Childs":[{"RJGHDLMCC":"交通水利用地","Childs":[{"SJGHDLMC":"铁路用地","Childs":[]}]},
{"RJGHDLMC":"其他建设用地","Childs":[{"SJGHDLMC":"风景名胜设施用地","Childs":[]},"SJGHDLMC":"特殊用地","Childs":[]}}]}]}

[快溜]

select '{YJGHDLMC:'+JGHDLMC+','+'RJGHDLMCC:'+RJGHDLMCC+','+'Childs:'+Childs+'}'
from tb for xml path('')

[missukiss02]

ls可以写明白点吗？

[帝企鹅已被占用]

建议使用存储过程。

[missukiss02]

帮忙顶顶，，不会写，痛苦中！

[AcHerat]

create table tb(YJGHDLMC varchar(20),RJGHDLMC varchar(20),SJGHDLMC varchar(20))
insert into tb
select '建设用地','交通水利用地','铁路用地' union all
select '建设用地','其他建设用地','风景名胜设施用地' union all
select '建设用地','其他建设用地','特殊用地' union all
select '农用地','城乡建设用地','其他独立' union all
select '农用地','耕地','旱地' union all
select '农用地','耕地','水浇地'
go

;with ach as
(
	select YJGHDLMC,RJGHDLMC,
		SJGHDLMC=stuff((select ',{"SJGHDLMC":"'+SJGHDLMC+'","Childs":[]}' from tb 
					where YJGHDLMC = t.YJGHDLMC and RJGHDLMC = t.RJGHDLMC for xml path('')),1,1,'')
	from tb t
	group by YJGHDLMC,RJGHDLMC
),art as
(
	select YJGHDLMC,
		RJGHDLMC=stuff((select ',{"RJGHDLMC":"'+RJGHDLMC+'","Childs":['+SJGHDLMC+']}' from ach 
					where YJGHDLMC=t.YJGHDLMC for xml path('')),1,1,'')
	from ach t
	group by YJGHDLMC
)

select '{"YJGHDLMC":"'+YJGHDLMC+'","Childs":['+RJGHDLMC+']}'
from art

drop table tb

/****************

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"YJGHDLMC":"建设用地","Childs":[{"RJGHDLMC":"交通水利用地","Childs":[{"SJGHDLMC":"铁路用地","Childs":[]}]},{"RJGHDLMC":"其他建设用地","Childs":[{"SJGHDLMC":"风景名胜设施用地","Childs":[]},{"SJGHDLMC":"特殊用地","Childs":[]}]}]}
{"YJGHDLMC":"农用地","Childs":[{"RJGHDLMC":"城乡建设用地","Childs":[{"SJGHDLMC":"其他独立","Childs":[]}]},{"RJGHDLMC":"耕地","Childs":[{"SJGHDLMC":"旱地","Childs":[]},{"SJGHDLMC":"水浇地","Childs":[]}]}]}

(2 行受影响)