CODEWITHSUNDEEP
sqloracle-databasejoin
Shahin P
Shahin P

Reputation: 367

To get list of employees with an id value different type as column name in output

I have a table empl with value such as:

empid   id_val      id_type
----------------------------
123456  ABCD1234XY  SSN
123456  U9876512    PASSPORT
123456  9090876543  UID
123456  V2345677    VOTERID
123457  ABCD1235YZ  SSN
123457  V8765120    PASSPORT
123457  8090876541  UID
123457  A2342627    VOTERID
123458  ABCD1236ZZ  SSN
123458  X9876510    PASSPORT
123458  9090876599  UID
123459  ABCD1238YY  SSN
123459  8080876549  UID
123459  Z2345678    VOTERID

I want output like list of ID type as column means:

empid   SSN         PASSPORT   UID         VOTERID
--------------------------------------------------
123456  ABCD1234XY  U9876512   9090876543  V2345677
123457  ABCD1235YZ  V8765120   8090876541  A2342627
123458  ABCD1236ZZ  X9876510   9090876599  NULL
123459  ABCD1238YY  NULL       8080876549  Z2345678

Could you please help me to get output like above format having value mention as top.

Upvotes: 0

Views: 60

Answers (2)

MT0
MT0

Reputation: 168257

You can use PIVOT:

SELECT *
FROM   table_name
PIVOT (
  MAX( id_val )
  FOR id_type IN (
    'SSN' AS ssn,
    'PASSPORT' AS passport,
    'UID' AS "UID",
    'VOTERID' AS voterid
  )
)
ORDER BY empid;

Which, for the sample data:

CREATE TABLE table_name ( empid, id_val, id_type ) AS
SELECT 123456,  'ABCD1234XY',  'SSN' FROM DUAL UNION ALL
SELECT 123456,  'U9876512',    'PASSPORT' FROM DUAL UNION ALL
SELECT 123456,  '9090876543',  'UID' FROM DUAL UNION ALL
SELECT 123456,  'V2345677',    'VOTERID' FROM DUAL UNION ALL
SELECT 123457,  'ABCD1235YZ',  'SSN' FROM DUAL UNION ALL
SELECT 123457,  'V8765120',    'PASSPORT' FROM DUAL UNION ALL
SELECT 123457,  '8090876541',  'UID' FROM DUAL UNION ALL
SELECT 123457,  'A2342627',    'VOTERID' FROM DUAL UNION ALL
SELECT 123458,  'ABCD1236ZZ',  'SSN' FROM DUAL UNION ALL
SELECT 123458,  'X9876510',    'PASSPORT' FROM DUAL UNION ALL
SELECT 123458,  '9090876599',  'UID' FROM DUAL UNION ALL
SELECT 123459,  'ABCD1238YY',  'SSN' FROM DUAL UNION ALL
SELECT 123459,  '8080876549',  'UID' FROM DUAL UNION ALL
SELECT 123459,  'Z2345678',    'VOTERID' FROM DUAL;

Outputs:

 EMPID | SSN        | PASSPORT | UID        | VOTERID 
-----: | :--------- | :------- | :--------- | :-------
123456 | ABCD1234XY | U9876512 | 9090876543 | V2345677
123457 | ABCD1235YZ | V8765120 | 8090876541 | A2342627
123458 | ABCD1236ZZ | X9876510 | 9090876599 | null    
123459 | ABCD1238YY | null     | 8080876549 | Z2345678

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You can use conditional aggregation:

select empid,
       max(case when id_val = 'SSN' then id_type end) as SSN,
       max(case when id_val = 'PASSPORT' then id_type end) as PASSPORT,
       max(case when id_val = 'UID' then id_type end) as UID,
       max(case when id_val = 'VOTERID' then id_type end) as VOTERID
from t
group by empid;

Upvotes: 1

Related Questions