CODEWITHSUNDEEP
sqloracle-database
Tamil Mani
Tamil Mani

Reputation: 64

Grouping by category

I have a table called data having a column called empid Select category from production

Empid
------
50011
50012
51011
52013
50014
55015
55016

In this data first 2 digit mentioned a department.. example 50 is one department 51 is another department i want to split id department wise in the below format,in Oracle SQL query

Dept0   Dept1.  Dept3.   dept4.  Dept5
50011.  51011   52013.   0.      55015
50012.   0.     0.       0.      55016 
50014.   0.     0.       0.          0

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

One method uses conditional aggregation using row_number():

select max(case when empid like '50%' then empid else 0 end) as dept0,
       max(case when empid like '51%' then empid else 0 end) as dept1,
       max(case when empid like '53%' then empid else 0 end) as dept3,
       max(case when empid like '54%' then empid else 0 end) as dept4,
       max(case when empid like '55%' then empid else 0 end) as dept5
from (select t.*,
             row_number() over (partition by substr(empid, 1, 2) order by empid) as seqnum
      from t
     ) t
group by seqnum;

Upvotes: 2

Related Questions