SQL SERVER查询到的数据转为Json格式

数据表如下：

Area 平台表：

Province 省份表：

District 区域表：

Area下级为：Province，Province下级为District

查询出的效果如下：

 

功能实现如下：（使用for xml来实现功能）

第一步：

--合并区域列,将合并的数据保存到临时表#temp_Province

select ProvinceID,ProvinceName,'{'+left(DistrictList,len(DistrictList)-1)+'}' as DistrictList,AreaID into #temp_Province
from (select ProvinceID,ProvinceName,AreaID,
   isnull((select '"District'+CAST(ROW_NUMBER() over(order by District.DistrictID) as nvarchar(50))+'":{'
      + '"DistrictID":"'+cast(DistrictID as nvarchar(100))+'",'
      +'"DistrictIName":"'+DistrictIName+'"},'
    from District where District.ProvinceID= Province.ProvinceID for xml path('')),'"District":"",') as DistrictList
   from Province)
as Province2

#temp_Province表查询到的数据如下：

第二步：

--合并省份列 将合并的数据保存到临时表#temp_Area
select AreaID,AreaName,'{'+left(AreaList,len(AreaList)-1)+'}' as AreaList into #temp_Area
from (select AreaID,AreaName,
     isnull((select '"Province' +CAST(ROW_NUMBER() over(order by #temp_Province.ProvinceID) as nvarchar(50))+'":{'
      +'"ProvinceID":"'+cast(ProvinceID as nvarchar(100))+'",'
      +'"ProvinceName":"'+ProvinceName+'",'
      +'"DistrictList":'+DistrictList+'},'
      from #temp_Province where Area.AreaID=#temp_Province.AreaID for xml path('')),'"Province":"",') as AreaList
       from Area)
as Area2

#temp_Area查询到的数据如下：

 

第三步：

--合并平台列,查询出最终的结果
select '{'+cast(stuff(
 (select ',"Area'+CAST(ROW_NUMBER() over(order by #temp_Area.AreaID) as nvarchar(50))+'":{'
     +'"AreaID":"'+cast(AreaID as nvarchar(100))+'",'
     +'"AreaName":"'+AreaName+'",'
     +'"AreaList":'+AreaList+'}'
 from #temp_Area  for xml path('')),1,1,'') as nvarchar(max))+'}'

第四步：

--删除临时表

drop table #temp_Province
drop table #temp_Area

最终查询到的效果如下：

{"Area1":{"AreaID":"4","AreaName":"华中","AreaList":{"Province":""}},"Area2":{"AreaID":"5","AreaName":"西北","AreaList":{"Province1":{"ProvinceID":"28","ProvinceName":"陕西","DistrictList":{"District1":{"DistrictID":"210","DistrictIName":"西安"},"District2":{"DistrictID":"211","DistrictIName":"咸阳"},"District3":{"DistrictID":"363","DistrictIName":"延安"},"District4":{"DistrictID":"364","DistrictIName":"榆林"}}},"Province2":{"ProvinceID":"39","ProvinceName":"青海","DistrictList":{"District":""}}}},"Area3":{"AreaID":"6","AreaName":"华南","AreaList":{"Province1":{"ProvinceID":"14","ProvinceName":"广西","DistrictList":{"District1":{"DistrictID":"154","DistrictIName":"玉林"}}},"Province2":{"ProvinceID":"15","ProvinceName":"海南","DistrictList":{"District1":{"DistrictID":"164","DistrictIName":"临高县"},"District2":{"DistrictID":"165","DistrictIName":"陵水黎族自治县"},"District3":{"DistrictID":"171","DistrictIName":"文昌市"},"District4":{"DistrictID":"172","DistrictIName":"五指山市"},"District5":{"DistrictID":"368","DistrictIName":"海南省直辖县"}}}}}}

 

 

得到需求要的效果：

 

 

注意：

以Area表为主表，串联3张表：

select Area.AreaID,Area.AreaName,Province.ProvinceID,Province.ProvinceName,District.DistrictID,District.DistrictIName from Area
left join Province on Province.AreaID=Area.AreaID
left join District on District.ProvinceID=Province.ProvinceID
order by Area.AreaID,Area.AreaName,Province.ProvinceID,Province.ProvinceName,District.DistrictID,District.DistrictIName

得到如图所示结果：

  有图可见：华中平台下是没有省份的，西北-青海下是没有区域的。

在创建临时表若没有对NULL的情况做判断，则会有数据丢失