SQL Server and JSON to generate Highmaps: very long and difficult to handle query

Question

SQL Server and JSON to generate Highmaps: very long and difficult to handle query

Navigation

#1 by (2 votes)

0

I set up the query below to have a JSON output assigned to Highmaps (from Highcharts), but it is very laborious and I would like to modify it if it is possible to make it smaller and easier to change it:

select
        count(case when maior_uf = 'AC' then natureza end) as 'br-ac',
        count(case when maior_uf = 'AC' and natureza = 'PMI' then natureza end) as 'pmi-br-ac',
        count(case when maior_uf = 'AC' and natureza = 'PPP' then natureza end) as 'ppp-br-ac',
        count(case when maior_uf = 'AC' and natureza = 'Concessão' then natureza end) as 'concessao-br-ac',
        count(case when maior_uf = 'AC' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-ac',
        count(case when maior_uf = 'AC' and nome_programa = 'PIEE' then natureza end) as 'piee-br-ac',

    count(case when maior_uf = 'AL' then natureza end) as 'br-al',
        count(case when maior_uf = 'AL' and natureza = 'PMI' then natureza end) as 'pmi-br-al',
        count(case when maior_uf = 'AL' and natureza = 'PMI' then natureza end) as 'ppp-br-al',
        count(case when maior_uf = 'AL' and natureza = 'PMI' then natureza end) as 'concessao-br-al',
        count(case when maior_uf = 'AL' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-al',
        count(case when maior_uf = 'AL' and nome_programa = 'PIEE' then natureza end) as 'piee-br-al',

    count(case when maior_uf = 'AM' then natureza end) as 'br-am',
        count(case when maior_uf = 'AM' and natureza = 'PMI' then natureza end) as 'pmi-br-am',
        count(case when maior_uf = 'AM' and natureza = 'PPP' then natureza end) as 'ppp-br-am',
        count(case when maior_uf = 'AM' and natureza = 'Concessão' then natureza end) as 'concessao-br-am',
        count(case when maior_uf = 'AM' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-am',
        count(case when maior_uf = 'AM' and nome_programa = 'PIEE' then natureza end) as 'piee-br-am',

    count(case when maior_uf = 'AP' then natureza end) as 'br-ap',
        count(case when maior_uf = 'AP' and natureza = 'PMI' then natureza end) as 'pmi-br-ap',
        count(case when maior_uf = 'AP' and natureza = 'PPP' then natureza end) as 'ppp-br-ap',
        count(case when maior_uf = 'AP' and natureza = 'Concessão' then natureza end) as 'concessao-br-ap',
        count(case when maior_uf = 'AP' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-ap',
        count(case when maior_uf = 'AP' and nome_programa = 'PIEE' then natureza end) as 'piee-br-ap',

    count(case when maior_uf = 'BA' then natureza end) as 'br-ba',
        count(case when maior_uf = 'BA' and natureza = 'PMI' then natureza end) as 'pmi-br-ba',
        count(case when maior_uf = 'BA' and natureza = 'PPP' then natureza end) as 'ppp-br-ba',
        count(case when maior_uf = 'BA' and natureza = 'Concessão' then natureza end) as 'concessao-br-ba',
        count(case when maior_uf = 'BA' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-ba',
        count(case when maior_uf = 'BA' and nome_programa = 'PIEE' then natureza end) as 'piee-br-ba',

    count(case when maior_uf = 'CE' then natureza end) as 'br-ce',
        count(case when maior_uf = 'CE' and natureza = 'PMI' then natureza end) as 'pmi-br-ce',
        count(case when maior_uf = 'CE' and natureza = 'PPP' then natureza end) as 'ppp-br-ce',
        count(case when maior_uf = 'CE' and natureza = 'Concessão' then natureza end) as 'concessao-br-ce',
        count(case when maior_uf = 'CE' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-ce',
        count(case when maior_uf = 'CE' and nome_programa = 'PIEE' then natureza end) as 'piee-br-ce',

    count(case when maior_uf = 'DF' then natureza end) as 'br-df',
        count(case when maior_uf = 'DF' and natureza = 'PMI' then natureza end) as 'pmi-br-df',
        count(case when maior_uf = 'DF' and natureza = 'PPP' then natureza end) as 'ppp-br-df',
        count(case when maior_uf = 'DF' and natureza = 'Concessão' then natureza end) as 'concessao-br-df',
        count(case when maior_uf = 'DF' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-df',
        count(case when maior_uf = 'DF' and nome_programa = 'PIEE' then natureza end) as 'piee-br-df',

    count(case when maior_uf = 'ES' then natureza end) as 'br-es',
        count(case when maior_uf = 'ES' and natureza = 'PMI' then natureza end) as 'pmi-br-es',
        count(case when maior_uf = 'ES' and natureza = 'PPP' then natureza end) as 'ppp-br-es',
        count(case when maior_uf = 'ES' and natureza = 'Concessão' then natureza end) as 'concessao-br-es',
        count(case when maior_uf = 'ES' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-es',
        count(case when maior_uf = 'ES' and nome_programa = 'PIEE' then natureza end) as 'piee-br-es',

    count(case when maior_uf = 'GO' then natureza end) as 'br-go',
        count(case when maior_uf = 'GO' and natureza = 'PMI' then natureza end) as 'pmi-br-go',
        count(case when maior_uf = 'GO' and natureza = 'PPP' then natureza end) as 'ppp-br-go',
        count(case when maior_uf = 'GO' and natureza = 'Concessão' then natureza end) as 'concessao-br-go',
        count(case when maior_uf = 'GO' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-go',
        count(case when maior_uf = 'GO' and nome_programa = 'PIEE' then natureza end) as 'piee-br-go',

    count(case when maior_uf = 'MA' then natureza end) as 'br-ma',
        count(case when maior_uf = 'MA' and natureza = 'PMI' then natureza end) as 'pmi-br-ma',
        count(case when maior_uf = 'MA' and natureza = 'PPP' then natureza end) as 'ppp-br-ma',
        count(case when maior_uf = 'MA' and natureza = 'Concessão' then natureza end) as 'concessao-br-ma',
        count(case when maior_uf = 'MA' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-ma',
        count(case when maior_uf = 'MA' and nome_programa = 'PIEE' then natureza end) as 'piee-br-ma',

    count(case when maior_uf = 'MG' then natureza end) as 'br-mg',
        count(case when maior_uf = 'MG' and natureza = 'PMI' then natureza end) as 'pmi-br-mg',
        count(case when maior_uf = 'MG' and natureza = 'PPP' then natureza end) as 'ppp-br-mg',
        count(case when maior_uf = 'MG' and natureza = 'Concessão' then natureza end) as 'concessao-br-mg',
        count(case when maior_uf = 'MG' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-mg',
        count(case when maior_uf = 'MG' and nome_programa = 'PIEE' then natureza end) as 'piee-br-mg',

    count(case when maior_uf = 'MS' then natureza end) as 'br-ms',
        count(case when maior_uf = 'MS' and natureza = 'PMI' then natureza end) as 'pmi-br-ms',
        count(case when maior_uf = 'MS' and natureza = 'PPP' then natureza end) as 'ppp-br-ms',
        count(case when maior_uf = 'MS' and natureza = 'Concessão' then natureza end) as 'concessao-br-ms',
        count(case when maior_uf = 'MS' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-ms',
        count(case when maior_uf = 'MS' and nome_programa = 'PIEE' then natureza end) as 'piee-br-ms',

    count(case when maior_uf = 'MT' then natureza end) as 'br-mt',
        count(case when maior_uf = 'MT' and natureza = 'PMI' then natureza end) as 'pmi-br-mt',
        count(case when maior_uf = 'MT' and natureza = 'PPP' then natureza end) as 'ppp-br-mt',
        count(case when maior_uf = 'MT' and natureza = 'Concessão' then natureza end) as 'concessao-br-mt',
        count(case when maior_uf = 'MT' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-mt',
        count(case when maior_uf = 'MT' and nome_programa = 'PIEE' then natureza end) as 'piee-br-mt',

    count(case when maior_uf = 'PA' then natureza end) as 'br-pa',
        count(case when maior_uf = 'PA' and natureza = 'PMI' then natureza end) as 'pmi-br-pa',
        count(case when maior_uf = 'PA' and natureza = 'PPP' then natureza end) as 'ppp-br-pa',
        count(case when maior_uf = 'PA' and natureza = 'Concessão' then natureza end) as 'concessao-br-pa',
        count(case when maior_uf = 'PA' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-pa',
        count(case when maior_uf = 'PA' and nome_programa = 'PIEE' then natureza end) as 'piee-br-pa',

    count(case when maior_uf = 'PB' then natureza end) as 'br-pb',
        count(case when maior_uf = 'PB' and natureza = 'PMI' then natureza end) as 'pmi-br-pb',
        count(case when maior_uf = 'PB' and natureza = 'PPP' then natureza end) as 'ppp-br-pb',
        count(case when maior_uf = 'PB' and natureza = 'Concessão' then natureza end) as 'concessao-br-pb',
        count(case when maior_uf = 'PB' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-pb',
        count(case when maior_uf = 'PB' and nome_programa = 'PIEE' then natureza end) as 'piee-br-pb',

    count(case when maior_uf = 'PE' then natureza end) as 'br-pe',
        count(case when maior_uf = 'PE' and natureza = 'PMI' then natureza end) as 'pmi-br-pe',
        count(case when maior_uf = 'PE' and natureza = 'PPP' then natureza end) as 'ppp-br-pe',
        count(case when maior_uf = 'PE' and natureza = 'Concessão' then natureza end) as 'concessao-br-pe',
        count(case when maior_uf = 'PE' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-pe',
        count(case when maior_uf = 'PE' and nome_programa = 'PIEE' then natureza end) as 'piee-br-pe',

    count(case when maior_uf = 'PI' then natureza end) as 'br-pi',
        count(case when maior_uf = 'PI' and natureza = 'PMI' then natureza end) as 'pmi-br-pi',
        count(case when maior_uf = 'PI' and natureza = 'PPP' then natureza end) as 'ppp-br-pi',
        count(case when maior_uf = 'PI' and natureza = 'Concessão' then natureza end) as 'concessao-br-pi',
        count(case when maior_uf = 'PI' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-pi',
        count(case when maior_uf = 'PI' and nome_programa = 'PIEE' then natureza end) as 'piee-br-pi',

    count(case when maior_uf = 'PR' then natureza end) as 'br-pr',
        count(case when maior_uf = 'PR' and natureza = 'PMI' then natureza end) as 'pmi-br-pr',
        count(case when maior_uf = 'PR' and natureza = 'PPP' then natureza end) as 'ppp-br-pr',
        count(case when maior_uf = 'PR' and natureza = 'Concessão' then natureza end) as 'concessao-br-pr',
        count(case when maior_uf = 'PR' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-pr',
        count(case when maior_uf = 'PR' and nome_programa = 'PIEE' then natureza end) as 'piee-br-pr',

    count(case when maior_uf = 'RJ' then natureza end) as 'br-rj',
        count(case when maior_uf = 'RJ' and natureza = 'PMI' then natureza end) as 'pmi-br-rj',
        count(case when maior_uf = 'RJ' and natureza = 'PPP' then natureza end) as 'ppp-br-rj',
        count(case when maior_uf = 'RJ' and natureza = 'Concessão' then natureza end) as 'concessao-br-rj',
        count(case when maior_uf = 'RJ' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-rj',
        count(case when maior_uf = 'RJ' and nome_programa = 'PIEE' then natureza end) as 'piee-br-rj',

    count(case when maior_uf = 'RN' then natureza end) as 'br-rn',
        count(case when maior_uf = 'RN' and natureza = 'PMI' then natureza end) as 'pmi-br-rn',
        count(case when maior_uf = 'RN' and natureza = 'PPP' then natureza end) as 'ppp-br-rn',
        count(case when maior_uf = 'RN' and natureza = 'Concessão' then natureza end) as 'concessao-br-rn',
        count(case when maior_uf = 'RN' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-rn',
        count(case when maior_uf = 'RN' and nome_programa = 'PIEE' then natureza end) as 'piee-br-rn',

    count(case when maior_uf = 'RO' then natureza end) as 'br-ro',
        count(case when maior_uf = 'RO' and natureza = 'PMI' then natureza end) as 'pmi-br-ro',
        count(case when maior_uf = 'RO' and natureza = 'PPP' then natureza end) as 'ppp-br-ro',
        count(case when maior_uf = 'RO' and natureza = 'Concessão' then natureza end) as 'concessao-br-ro',
        count(case when maior_uf = 'RO' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-ro',
        count(case when maior_uf = 'RO' and nome_programa = 'PIEE' then natureza end) as 'piee-br-ro',

    count(case when maior_uf = 'RR' then natureza end) as 'br-rr',
        count(case when maior_uf = 'RR' and natureza = 'PMI' then natureza end) as 'pmi-br-rr',
        count(case when maior_uf = 'RR' and natureza = 'PPP' then natureza end) as 'ppp-br-rr',
        count(case when maior_uf = 'RR' and natureza = 'Concessão' then natureza end) as 'concessao-br-rr',
        count(case when maior_uf = 'RR' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-rr',
        count(case when maior_uf = 'RR' and nome_programa = 'PIEE' then natureza end) as 'piee-br-rr',

    count(case when maior_uf = 'RS' then natureza end) as 'br-rs',
        count(case when maior_uf = 'RS' and natureza = 'PMI' then natureza end) as 'pmi-br-rs',
        count(case when maior_uf = 'RS' and natureza = 'PPP' then natureza end) as 'ppp-br-rs',
        count(case when maior_uf = 'RS' and natureza = 'Concessão' then natureza end) as 'concessao-br-rs',
        count(case when maior_uf = 'RS' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-rs',
        count(case when maior_uf = 'RS' and nome_programa = 'PIEE' then natureza end) as 'piee-br-rs',

    count(case when maior_uf = 'SC' then natureza end) as 'br-sc',
        count(case when maior_uf = 'SC' and natureza = 'PMI' then natureza end) as 'pmi-br-sc',
        count(case when maior_uf = 'SC' and natureza = 'PPP' then natureza end) as 'ppp-br-sc',
        count(case when maior_uf = 'SC' and natureza = 'Concessão' then natureza end) as 'concessao-br-sc',
        count(case when maior_uf = 'SC' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-sc',
        count(case when maior_uf = 'SC' and nome_programa = 'PIEE' then natureza end) as 'piee-br-sc',

    count(case when maior_uf = 'SE' then natureza end) as 'br-se',
        count(case when maior_uf = 'SE' and natureza = 'PMI' then natureza end) as 'pmi-br-se',
        count(case when maior_uf = 'SE' and natureza = 'PPP' then natureza end) as 'ppp-br-se',
        count(case when maior_uf = 'SE' and natureza = 'Concessão' then natureza end) as 'concessao-br-se',
        count(case when maior_uf = 'SE' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-se',
        count(case when maior_uf = 'SE' and nome_programa = 'PIEE' then natureza end) as 'piee-br-se',

    count(case when maior_uf = 'SP' then natureza end) as 'br-sp',
        count(case when maior_uf = 'SP' and natureza = 'PMI' then natureza end) as 'pmi-br-sp',
        count(case when maior_uf = 'SP' and natureza = 'PPP' then natureza end) as 'ppp-br-sp',
        count(case when maior_uf = 'SP' and natureza = 'Concessão' then natureza end) as 'concessao-br-sp',
        count(case when maior_uf = 'SP' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-sp',
        count(case when maior_uf = 'SP' and nome_programa = 'PIEE' then natureza end) as 'piee-br-sp',

    count(case when maior_uf = 'TO' then natureza end) as 'br-to',
        count(case when maior_uf = 'TO' and natureza = 'PMI' then natureza end) as 'pmi-br-to',
        count(case when maior_uf = 'TO' and natureza = 'PPP' then natureza end) as 'ppp-br-to',
        count(case when maior_uf = 'TO' and natureza = 'Concessão' then natureza end) as 'concessao-br-to',
        count(case when maior_uf = 'TO' and nome_programa = 'PIL' then natureza end) as 'nome_programa-br-to',
        count(case when maior_uf = 'TO' and nome_programa = 'PIEE' then natureza end) as 'piee-br-to'
from [banco_de_dados].[dbo].[tabela_db]

Notice that the query is huge and the output is as follows:

Noticethattheoutputisasingleline.

Then,IrunaPHPthatrunsthisquerytobeabletogenerateaJSON.

JSONresult:

[{
    "hc-key": "br-ac",
    "sigla": "AC",
    "value": [1],
    "value1": [1],
    "value2": [0],
    "value3": [0],
    "value4": [1],
    "value5": [0]
}, {
    "hc-key": "br-al",
    "sigla": "AL",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-am",
    "sigla": "AM",
    "value": [1],
    "value1": [1],
    "value2": [0],
    "value3": [0],
    "value4": [1],
    "value5": [0]
}, {
    "hc-key": "br-ap",
    "sigla": "AP",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-ba",
    "sigla": "BA",
    "value": [6],
    "value1": [3],
    "value2": [0],
    "value3": [3],
    "value4": [3],
    "value5": [2]
}, {
    "hc-key": "br-ce",
    "sigla": "CE",
    "value": [3],
    "value1": [0],
    "value2": [0],
    "value3": [3],
    "value4": [0],
    "value5": [2]
}, {
    "hc-key": "br-df",
    "sigla": "DF",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-es",
    "sigla": "ES",
    "value": [1],
    "value1": [1],
    "value2": [0],
    "value3": [0],
    "value4": [1],
    "value5": [0]
}, {
    "hc-key": "br-go",
    "sigla": "GO",
    "value": [7],
    "value1": [4],
    "value2": [0],
    "value3": [3],
    "value4": [5],
    "value5": [2]
}, {
    "hc-key": "br-ma",
    "sigla": "MA",
    "value": [2],
    "value1": [1],
    "value2": [0],
    "value3": [1],
    "value4": [1],
    "value5": [1]
}, {
    "hc-key": "br-mg",
    "sigla": "MG",
    "value": [9],
    "value1": [4],
    "value2": [1],
    "value3": [4],
    "value4": [2],
    "value5": [3]
}, {
    "hc-key": "br-ms",
    "sigla": "MS",
    "value": [6],
    "value1": [3],
    "value2": [0],
    "value3": [3],
    "value4": [3],
    "value5": [3]
}, {
    "hc-key": "br-mt",
    "sigla": "MT",
    "value": [5],
    "value1": [0],
    "value2": [1],
    "value3": [4],
    "value4": [1],
    "value5": [3]
}, {
    "hc-key": "br-pa",
    "sigla": "PA",
    "value": [9],
    "value1": [3],
    "value2": [0],
    "value3": [6],
    "value4": [3],
    "value5": [6]
}, {
    "hc-key": "br-pb",
    "sigla": "PB",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-pe",
    "sigla": "PE",
    "value": [3],
    "value1": [2],
    "value2": [0],
    "value3": [1],
    "value4": [2],
    "value5": [1]
}, {
    "hc-key": "br-pi",
    "sigla": "PI",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-pr",
    "sigla": "PR",
    "value": [10],
    "value1": [4],
    "value2": [0],
    "value3": [6],
    "value4": [4],
    "value5": [4]
}, {
    "hc-key": "br-rj",
    "sigla": "RJ",
    "value": [14],
    "value1": [8],
    "value2": [1],
    "value3": [5],
    "value4": [8],
    "value5": [0]
}, {
    "hc-key": "br-rn",
    "sigla": "RN",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-ro",
    "sigla": "RO",
    "value": [4],
    "value1": [3],
    "value2": [0],
    "value3": [1],
    "value4": [3],
    "value5": [1]
}, {
    "hc-key": "br-rr",
    "sigla": "RR",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-rs",
    "sigla": "RS",
    "value": [6],
    "value1": [2],
    "value2": [0],
    "value3": [4],
    "value4": [3],
    "value5": [2]
}, {
    "hc-key": "br-sc",
    "sigla": "SC",
    "value": [8],
    "value1": [4],
    "value2": [2],
    "value3": [2],
    "value4": [5],
    "value5": [0]
}, {
    "hc-key": "br-se",
    "sigla": "SE",
    "value": [0],
    "value1": [0],
    "value2": [0],
    "value3": [0],
    "value4": [0],
    "value5": [0]
}, {
    "hc-key": "br-sp",
    "sigla": "SP",
    "value": [24],
    "value1": [7],
    "value2": [3],
    "value3": [14],
    "value4": [14],
    "value5": [1]
}, {
    "hc-key": "br-to",
    "sigla": "TO",
    "value": [2],
    "value1": [1],
    "value2": [0],
    "value3": [1],
    "value4": [1],
    "value5": [1]
}]

The important part is br-sigla_estado that makes the JSON binding with a property of Highmaps hc-key .

php sql-server json highcharts

asked by anonymous 18.01.2016 / 01:38

1 answer

Error connecting to WebService + Soap + Android Apache2 error in linux

score 2 · Accepted Answer

The query can simply be

select
    maior_uf AS estado,
    count(*) as total_estado,
    count(case when natureza = 'PMI' then natureza end) as 'pmi-br',
    count(case when natureza = 'PPP' then natureza  end) as 'ppp-br',
    count(case when natureza = 'Concessão' then natureza  end) as 'concessao-br',
    count(case when nome_programa = 'PIL' then natureza  end) as 'nome_programa-br',
    count(case when nome_programa = 'PIEE' then natureza end) as 'piee-br'
from [banco_de_dados].[dbo].[tabela_db]
group by maior_uf
order by maior_uf

(I'm without SQL Server to test now, but I think that's it)

This generates a column with the state, one with the overall state, and another 5 with the most specific values you are looking for, for that state. With this you rework PHP to generate the same JSON.