Database Reference
In-Depth Information
and [geo region] = @RegionName
--and CountryName = 'Austria'
order by CountryName asc
UPDATE @tbl
SET @return = @return+json, FullJSON = @return
RETURN @return
END
go
In this code, the function takes a template JSON in the following format:
"{
"children": [],
"data": {
"GDP Per Capita": "56",
"$color": "#FDAE61",
"image": "",
"$area": 56
}"
and uses values for the GDP per Capita and National Reserve to set the color
and area. A
case
statement for 11 ranges of color is set up to set the color.
There is also a chunk of code that appends each of the individual countries
JSON to the
@return
variable. The
RowNumber()
call adds a comma to each
entry except the first.
The following is the stored procedure that uses this function:
ALTER PROC TreeMapByRegion
as
DECLARE @return varchar(max) = ''
DECLARE @tbl Table(RegionName varchar(255), JSON varchar(max),
FUllJson varchar(max))
INSERT INTO @tbl(RegionName, JSON)
select [GEO Region] ,
CASE WHEN ROW_NUMBER () over (order by [GEO Region]) = 1 THEN ''
ELSE ',' END +
' {
"children": [' +
