Need to arrange employee names as per their city c

Question

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:

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.

Expected Output:

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

Appreciate if anyone can guide me in the right direction.

Answer 1

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

Answer 2

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.

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.

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).

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.

Good luck!

Answer 3

Maybe you need to transpose your result. See this link . I think DECODE or CASE works best for your 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

Answer 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.

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.

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.

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.