CODEWITHSUNDEEP
sqlansi-sql
lab bhattacharjee
lab bhattacharjee

Reputation: 1677

Custom group by query

I have a table containing three columns: EmployeeID, Department, Location

Each employee potentially can have multiple departments and can be located in more than one locations.

By SQL query on any RDBMS, I need return the distinct set of Departments(separated by hyphen), the distinct set of Locations(separated by hyphen) against each EmployeeID.

For the following record in DB,

  1. EMP1, HR, Kolkata

  2. EMP1, HR, Delhi

  3. EMP1, Facility, Mumbai

  4. EMP1, Facility, Kolkata

I need to get the following result : EMP1, HR-Facility, Kolkata-Delhi-Mumbai

Any help will be more than welcome.

Upvotes: 1

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The only way that I can think of doing this in ANSI SQL is using dense_rank() and conditional aggregation. Assuming there are no more than three values for each group:

select empid,
       trim(trailing '-' from
            concat( max(case when l_seqnum = 1 then concat(location, '-') else '' end),
                    max(case when l_seqnum = 2 then concat(location, '-') else '' end),
                    max(case when l_seqnum = 3 then concat(location, '-') else '' end)
           ) as locations,
       trim(trailing '-' from
            concat( max(case when d_seqnum = 1 then concat(department, '-') else '' end),
                    max(case when d_seqnum = 2 then concat(department, '-') else '' end),
                    max(case when d_seqnum = 3 then concat(department, '-') else '' end)
           ) as departments
from (select t.*,
             dense_rank() over (partition by empid order by department) as d_seqnum,
             dense_rank() over (partition by empid order by location) as l_seqnum
      from table t
     ) t
group by empid;

However, most databases have more reasonable ways to express this.

Upvotes: 1

Related Questions