需要按照他们所在的城市列明智地安排员工姓名 [英] Need to arrange employee names as per their city column wise

问题描述

我编写了一个查询，该查询从城市名称的不同列组中提取数据.

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

我的查询如下:

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:

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.

预期输出:

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

感谢任何人都可以引导我朝正确的方向前进.

Appreciate if anyone can guide me in the right direction.

推荐答案

通常，我会编辑"我的第一个答案，但是问题已经改变了很多，它与原始答案有很大的不同，因此我的旧答案不能已编辑"-现在需要一个全新的答案.

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.

您可以通过旋转来做您想做的事情，如下所示.想知道为什么要在基本SQL中而不是通过使用专门针对报告需求编写的报告工具来执行此操作.

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.

您将在Chicago栏中两次看到"York"(约克)；您将意识到这是有目的的(您将在代码顶部的测试"表中看到我有重复的行)；这是为了证明您的安排可能存在缺陷.

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.

在询问是否可以获取列表但没有行号之前-首先，如果仅生成一组行，则这些行将不排序. ，您可以执行我的操作，然后从我编写的查询中选择select "'DFW'", "'CHI'", "'NY'".关系理论和SQL标准不能不保证行顺序将被保留，但是Oracle显然确实保留了该行顺序，至少在当前版本中如此.您可以自行承担使用该解决方案的风险.

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.

max(name)对于未初始化的子类可能看起来很奇怪； Oracle中PIVOT运算符的怪异局限性在于，即使是在一组完全相同的元素上，它也需要使用聚合函数.

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.

代码如下:

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
/

输出:

        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.

这篇关于需要按照他们所在的城市列明智地安排员工姓名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！