如何在SQL中使用CASE逻辑将单元格值组合到列名 [英] How to combine cell value to column name with CASE logic in SQL

问题描述

我有两个视图，数据表示如下:

I have two VIEWS where data is represented as below:

查看1

| username   |  function   |   level      | location       | 
|:-----------|------------:|:------------:| :-------------:|
| John       |   Operation |  Country     | United Kingdom | 
| John       |   Operation |  Area        | South West     |
| John       |   Operation |  Shop        | 0001           |

查看2

| shop       |  region     |   country       |    area        | 
|:-----------|------------:|:---------------:| :-------------:|
| 1200       |   u1        |  United Kingdom |    West        | 
| 1201       |   u2        |  United Kingdom |    West        |
| 1000       |   f1        |  France         |  South West    | 
| 1100       |   i1        |  Italy          |  South West    |
| 1111       |   s1        |  Spain          |  South West    | 
| 1112       |   n2        |  Norway         |  South West    |
| 0001       |   o1        |  Japan          |  Asia          |

两个VIEW之间的关系是，每个区域有一个以上的国家，每个国家有一个以上的区域，每个区域有一个以上的商店.

The relation between the two VIEWs are that each AREA has more than one Country, each Country has more than one REGION, each REGION has more than one shop.

尝试::生成一个视图，其中将根据顶部表中的LEVEL选择，从顶部表数据为每个用户生成所有行.因此，在这种情况下，VIEW应该显示

Trying to: Generate a view where for each user from the top table data all rows will be generated from the bottom table depending on the LEVEL selection on the top table. So in this case the VIEW should show

预期结果:因此，在这种情况下，VIEW应显示西南地区下的所有商店，地区，国家，英国国家下的所有商店和地区以及商店0001.

Expected Result: So in this case the VIEW should show all the SHOPS, REGIONS, COUNTRIES under AREA South West, all the SHOPS and REGIONS under COUNTRY United Kingdom and the SHOP 0001.

| username   |  function   |   level         |    location    | 
|:-----------|------------:|:---------------:| :-------------:|
| John       |   Operation |    shop         |    0001        |
| John       |   Operation |    shop         |    1001        |
| John       |   Operation |    shop         |    ...         |
| John       |   Operation |    Country      |  United Kingdom|
| John       |   Operation |    Country      |    ...         |
| John       |   Operation |    Country      |    ...         |
| John       |   Operation |    Region       |    ...         |
| John       |   Operation |    Region       |    ...         |
| John       |   Operation |    Region       |    ...         |
| John       |   Operation |    Area         |    South West  | 
| John       |   Operation |    Area         |    ...         | 
| John       |   Operation |    Area         |    ...         | 

不确定如何使用CASE语句完成此操作.任何帮助，将不胜感激.

Not sure how this can be done with CASE statement. Any help would be appreciated.

推荐答案

我认为这需要四个独立查询的联合:​​

I think this will require a union of four separate queries:

• 第一个查询应选择与第一个表连接的第二个表中等于或在第一个表中指定的商店/地区/国家/地区以下的所有商店.
• 第二个查询应选择等于或小于第一个表中指定的与第二个表连接的区域/国家/地区的所有区域.
• 第三个查询应选择等于或小于第一个表中指定的与第二个表连接的国家/地区的所有国家/地区.
• 第四个查询应选择在第一个表中指定的所有区域，并连接到第二个表.

每个查询中可能存在重复项，因此我们应在每个查询中使用distinct消除重复项.在查询之间不能有重复项，因此我们可以使用union all而不是union.

There could be duplicates within each query, so we should use distinct within each query to eliminate them. There cannot be duplicates between the queries, so we can use union all rather than union.

create table userop (username varchar(32), [function] varchar(32), level varchar(32), location varchar(32) );
create table shopgeo (shop varchar(32), region varchar(32), country varchar(32), area varchar(32) );

insert into userop (username,[function],level,location) values
    ('John','Operation','Country','United Kingdom'),
    ('John','Operation','Area','South West'),
    ('John','Operation','Shop','0001')
;

insert into shopgeo (shop,region,country,area) values
    ('1200','u1','United Kingdom','West'),
    ('1201','u2','United Kingdom','West'),
    ('1000','f1','France','South West'),
    ('1100','i1','Italy','South West'),
    ('1111','s1','Spain','South West'),
    ('1112','n2','Norway','South West'),
    ('0001','o1','Japan','Asia')
;

-- show base tables
select * from userop;
select * from shopgeo;

-- solution
select * from (
    select distinct o.username, o.[function], 'shop' level, g.shop location from userop o inner join shopgeo g on o.location=case when o.level='Area' then g.area when o.level='Country' then g.country when o.level='Region' then g.region when o.level='Shop' then g.shop end
    union all select distinct o.username, o.[function], 'region' level, g.region from userop o inner join shopgeo g on o.location=case when o.level='Area' then g.area when o.level='Country' then g.country when o.level='Region' then g.region end
    union all select distinct o.username, o.[function], 'country' level, g.country from userop o inner join shopgeo g on o.location=case when o.level='Area' then g.area when o.level='Country' then g.country end
    union all select distinct o.username, o.[function], 'area' level, g.area from userop o inner join shopgeo g on o.location=case when o.level='Area' then g.area end
) t1 order by username, case when level='shop' then 1 when level='country' then 2 when level='region' then 3 when level='area' then 4 end, location;

这篇关于如何在SQL中使用CASE逻辑将单元格值组合到列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！