当前位置: 动力学知识库 > 问答 > 编程问答 >

SQL aggregate rows in one row with different name

问题描述:

I have a CTE that returns the following information that is a result of several joins:

 city Value1 Value2

Aveiro 83 1624

Beja 6 83

Braga 70 1751

Bragança 8 165

Castelo Branco 7 206

Coimbra 32 573

Évora 14 173

Faro 181 3284

Guarda 2 106

Ilha da Graciosa 39 92

Ilha da Madeira 520 3339

Ilha das Flores 53 154

Ilha de Porto Santo 46 107

Ilha de Santa Maria 114 140

Ilha de São Jorge 31 116

Ilha de São Miguel 306 2582

Ilha do Corvo 7 12

Ilha do Faial 90 217

Ilha do Pico 123 167

Ilha Terceira 205 493

Leiria 82 1167

Lisboa 301 8833

Portalegre 11 201

Porto 333 5966

Santarém 39 973

Setúbal 90 2229

Viana do Castelo 24 435

Vila Real 30 473

Viseu 40 640

Basically they return some values by city and islands and I would like to join the values of some islands in one row with the following rule:

Madeira = Ilha de Porto Santo + Ilha da Madeira

Açores =

Ilha da Graciosa

Ilha das Flores

Ilha de Santa Maria

Ilha de São Jorge

Ilha de São Miguel

Ilha do Corvo

Ilha do Faial

Ilha do Pico

Ilha Terceira

The output should be:

city Value1 Value2

Aveiro 83 1624

Beja 6 83

Braga 70 1751

Bragança 8 165

Castelo Branco 7 206

Coimbra 32 573

Évora 14 173

Faro 181 3284

Guarda 2 106

Madeira 566 3446

Açores 968 3973

Leiria 82 1167

Lisboa 301 8833

Portalegre 11 201

Porto 333 5966

Santarém 39 973

Setúbal 90 2229

Viana do Castelo 24 435

Vila Real 30 473

Viseu 40 640

I try several ideas based on same posts using FOR XML PATH but I was not able to reproduce the expect result .

I don't have any ID that can be used in GROUP since Island has their own ID

Can anyone help me?

Thank you

网友答案:

You can use a case:

select (case when city in ('Ilha de Porto Santo', 'Ilha da Madeira')
             then 'Madeira'
             when city in ('Ilha da Graciosa', 'Ilha das Flores', 'Ilha de Santa Maria',
                           'Ilha de São Jorge', 'Ilha de São Miguel', 'Ilha do Corvo',
                           'Ilha do Faial', 'Ilha do Pico', 'Ilha Terceira')
             then 'Açores'
             else city
        end) as city
       sum(Value1) as value1, sum(Value2) as value2
from cte
group by (case when city in ('Ilha de Porto Santo', 'Ilha da Madeira')
               then 'Madeira'
               when city in ('Ilha da Graciosa', 'Ilha das Flores', 'Ilha de Santa Maria',
                             'Ilha de São Jorge', 'Ilha de São Miguel', 'Ilha do Corvo',
                             'Ilha do Faial', 'Ilha do Pico', 'Ilha Terceira')
               then 'Açores'
               else city
          end);

Hmmm, thinking about this, you should probably define a reference table that has the "detailed city" and the "reporting city". This will make it easier to maintain the code in the future -- the rules about what to combine will be in a table (easy to change) rather than in code (harder to change).

分享给朋友:
您可能感兴趣的文章:
随机阅读: