需要根据城市列明智地安排员工姓名

I have written a query which extracts the data from different columns group by city name.

我编写了一个查询，它按城市名称从不同的列中提取数据。

My query is as follows:

我的查询如下：

select q.first_name 
from (select employee_id as eid,first_name,city 
      from employees 
       group by city,first_name,employee_id 
       order by first_name)q
      , employees e 
where e.employee_id = q.eid;

The output of the query is employee names in a single column grouped by their cities.

查询的输出是按其城市分组的单个列中的员工姓名。

Now I would like to enhance the above query to classify the employees by their city names in different columns.

现在我想增强上面的查询，按照不同列中的城市名称对员工进行分类。

I tried using pivot to make this work. Here is my pivot query:

我尝试使用pivot来完成这项工作。这是我的透视查询：

select * from ( 
    select q.first_name 
    from (select employee_id as eid,first_name,city 
          from employees 
          group by city,first_name,employee_id  
          order by first_name)q
        , employees e 
       where e.employee_id = q.eid
 ) pivot 
    (for city in (select city from employees))

I get some syntax issue saying missing expression and I am not sure how to use pivot to achieve the below expected output.

我得到一些语法问题，说缺少表达式，我不知道如何使用pivot来实现以下预期的输出。

Expected Output:

预期产出：

DFW                     CH                  NY
----                    ---                 ---
TripeH                  John                Hitman
Batista                 Cena                Yokozuna
Rock                    James               Mysterio

Appreciate if anyone can guide me in the right direction.

感谢是否有人能指导我朝着正确的方向前进。

4 个解决方案

#1

Unfortunately what you are trying to do is not possible, at least not in "straight" SQL - you would need dynamic SQL, or a two-step process (in the first step generating a string that is a new SQL statement). Complicated.

不幸的是，你要做的事情是不可能的，至少在“直接”SQL中是不可能的 - 你需要动态SQL或两步过程（在第一步中生成一个新SQL语句的字符串）。复杂。

The problem is that you are not including a fixed list of city names (as string literals). You are trying to create columns based on whatever you get from (select city from employees). Thus the number of columns and the name of the columns is not known until the Oracle engine reads the data from the table, but before the engine starts it must already know what all the columns will be. Contradiction.

问题是您没有包含固定的城市名称列表（作为字符串文字）。您正在尝试根据您获得的任何内容创建列（从员工中选择城市）。因此，在Oracle引擎从表中读取数据之前，不知道列的数量和列的名称，但在引擎启动之前，它必须已经知道所有列的内容。矛盾。

Note also that if this was possible, you almost surely would want (select distinct city from employees).

另请注意，如果可行，您几乎肯定会想要（从员工中选择不同的城市）。

ADDED: The OP asks a follow-up question in a comment (see below).

增加：OP在评论中询问后续问题（见下文）。

The ideal arrangement is for the cities to be in their own, smaller table, and the "city" in the employees table to have a foreign key constraint so that the "city" thing is manageable. You don't want one HR clerk to enter New York, another to enter New York City and a third to enter NYC for the same city. One way or the other, first try your code by replacing the subquery that follows the operator IN in the pivot clause with simply the comma-separated list of string literals for the cities: ... IN ('DFW', 'CH', 'NY'). Note that the order in which you put them in this list will be the order of the columns in the output. I didn't check the entire query to see if there are any other issues; try this and let us know what happens.

理想的安排是让城市在他们自己的小桌子中，并且雇员桌子中的“城市”具有外键约束，以便“城市”的东西是可管理的。您不希望一名HR职员进入纽约，另一名HR职员进入纽约市，第三名进入纽约市进入同一城市。不管怎样，首先尝试代码，方法是在pivot子句中用简单的逗号分隔的字符串文字列表替换跟随运算符IN的子查询：... IN（'DFW'，'CH'， 'NY'）。请注意，将它们放入此列表的顺序将是输出中列的顺序。我没有检查整个查询，看看是否还有其他问题;试试这个，让我们知道会发生什么。

Good luck!

祝你好运！

#2

select
(CASE WHEN CITY="DFW" THEN EMPLOYEE_NAME END) DFW,
(CASE WHEN CITY="CH" THEN EMPLOYEE_NAME END) CH,
(CASE WHEN CITY="NY" THEN EMPLOYEE_NAME END) NY
FROM employees
order by first_name

选择（例如，当城市=“DFW”然后EMPLOYEE_NAME结束时）DFW，（当城市=“CH”然后EMPLOYEE_NAME结束时）CH，（例如城市=“纽约”然后EMPLOYEE_NAME结束）NY FROM员工通过first_name订购

#3

Maybe you need to transpose your result. See this link . I think DECODE or CASE works best for your case:

也许你需要转置你的结果。看到这个链接。我认为DECODE或CASE最适合您的情况：

select 
(CASE WHEN CITY="DFW" THEN EMPLOYEE_NAME END) DFW,
(CASE WHEN CITY="CH" THEN EMPLOYEE_NAME END) CH,
(CASE WHEN CITY="NY" THEN EMPLOYEE_NAME END) NY
FROM employees
order by first_name

#4

Normally I would "edit" my first answer, but the question has changed so much, it's quite different from the original one so my older answer can't be "edited" - this now needs a completely new answer.

通常情况下，我会“编辑”我的第一个答案，但问题已经发生了很大变化，与原来的问题完全不同，所以我的旧答案无法“编辑” - 现在需要一个全新的答案。

You can do what you want with pivoting, as I show below. Wondering why you want to do this in basic SQL and not by using reporting tools, which are written specifically for reporting needs. There's no way you need to keep your data in the pivoted format in the database.

你可以通过旋转来做你想做的事，如下所示。想知道为什么要在基本SQL中执行此操作，而不是使用专门为报告需求编写的报告工具。您无需在数据库中以数据格式保存数据。

You will see 'York' twice in the Chicago column; you will recognize that's on purpose (you will see I had a duplicate row in the "test" table at the top of my code); this is to demonstrate a possible defect of your arrangement.

您将在芝加哥专栏中看到“约克”两次;你会意识到这是故意的（你会看到我的代码顶部的“测试”表中有一个重复的行）;这是为了证明您的安排可能存在缺陷。

Before you ask if you could get the list but without the row numbers - first, if you are simply generating a set of rows, those are not ordered. If you want things ordered for reporting purposes, you can do what I did, and then select "'DFW'", "'CHI'", "'NY'" from the query I wrote. Relational theory and the SQL standard do not guarantee the row order will be preserved, but Oracle apparently does preserve it, at least in current versions; you can use that solution at your own risk.

在您询问是否可以获取列表但没有行号之前 - 首先，如果您只是生成一组行，则不会对它们进行排序。如果你想要为报告目的订购的东西，你可以做我做的，然后从我写的查询中选择“'DFW'”，“'CHI'”，“'NY'”。关系理论和SQL标准不保证行顺序将被保留，但Oracle显然确实保留了它，至少在当前版本中是这样;您可以使用该解决方案，风险自负。

max(name) in the pivot clause may look odd to the uninitiated; one of the weird limitations of the PIVOT operator in Oracle is that it requires an aggregate function to be used, even if it's over a set of exactly one element.

pivot子句中的max（name）对于不熟悉的人来说可能看起来很奇怪; Oracle中PIVOT运算符的一个奇怪限制是，它需要使用聚合函数，即使它只是一组恰好一个元素。

Here's the code:

这是代码：

with t (city, name) as     -- setting up input data for testing
   (
      select 'DFW', 'Smith'     from dual union all
      select 'CHI', 'York'      from dual union all
      select 'DFW', 'Matsumoto' from dual union all
      select 'NY',  'Abu Osman' from dual union all
      select 'DFW', 'Adams'     from dual union all
      select 'CHI', 'Wilson'    from dual union all
      select 'CHI', 'Arenas'    from dual union all
      select 'NY',  'Theodore'  from dual union all
      select 'CHI', 'McGhee'    from dual union all
      select 'NY',  'Zhou'      from dual union all
      select 'NY' , 'Simpson'   from dual union all
      select 'CHI', 'Narayanan' from dual union all
      select 'CHI', 'York'      from dual union all
      select 'NY',  'Perez'     from dual
   )
select * from
   (
      select row_number() over (partition by city order by name) rn, 
             city, name 
      from t
   )
pivot (max(name) for city in ('DFW', 'CHI', 'NY') )
order by rn
/

And the output:

并输出：

        RN 'DFW'     'CHI'     'NY'
---------- --------- --------- ---------
         1 Adams     Arenas    Abu Osman
         2 Matsumoto McGhee    Perez
         3 Smith     Narayanan Simpson
         4           Wilson    Theodore
         5           York      Zhou
         6           York

6 rows selected.