[SQL] Select Distinct Values from one Column and Output SQL to Multiple Columns

[oracledba1]

DLA_PROFESSIONAL_A Table has many columns FIRST_NAME,MIDDLE_NAME, LAST_NAME, TITLE_CODE, COMMUNICATION_TYPE_CODE, COMMUNICATION_VALUE


DLA_PROFESSIONAL_A Table


This is just a sample. There is 500,00 records like this for COMMUNICATION_TYPE_CODE, and COMMUNICATION_VALUE column


Telephone = PHONE1
Cellular = PHONE2
E-mail = EMAIL1
Fax = FAX1


SELECT 'IMD Data Source' as DATA_SOURCE,
FIRST_NAME as FIRST_NM,
MIDDLE_NAME as MIDDLE_NM,
LAST_NAME as LAST_NM,
TITLE_CODE as CREDENTIALS,
PHONE1,
PHONE2,
EMAIL1,
FAX1,
FROM DLA_PROFESSIONAL_A

I already mapped the FIRST_NAME,MIDDLE_NAME, LAST_NAME, TITLE_CODE to target columns in my select statement so dont worry about that. I am having problems mapping PHONE1,PHONE2,EMAIL1,FAX1

I have to pick out the communication value column data (phone number,email,fax,cellular) when comunication type code is ( Telephone,Fax,E-mail,Cellular)

THIS HAS TO GO TO 4 SEPERATE COLUMNS (When COMMUNICATION_TYPE_CODE='Telephone' I need to pull COMMUNICATION_VALUE data from the column and map that to
PHONE1 column in output, When COMMUNICATION_TYPE_CODE='Cellular' I need to pull COMMUNICATION_VALUE data from the column and map that to
PHONE2 column in output, When COMMUNICATION_TYPE_CODE='E-mail' I need to pull COMMUNICATION_VALUE data from the column and map that to
EMAIL1 column in output, When COMMUNICATION_TYPE_CODE='Fax' I need to pull COMMUNICATION_VALUE data from the column and map that to
EMAIL1 column in output )

I have the separate sequels I dont know how to put it in to the original select statement.




SELECT DISTINCT COMMUNICATION_VALUE FROM DG_SP_NP.DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='Telephone'

SELECT DISTINCT COMMUNICATION_VALUE FROM DG_SP_NP.DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='Cellular'

SELECT DISTINCT COMMUNICATION_VALUE FROM DG_SP_NP.DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='E-mail'

SELECT DISTINCT COMMUNICATION_VALUE FROM DG_SP_NP.DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='Fax'

I want to do something like this. This is not the right sql for the 4 outputs but you get the idea.


SELECT 'IMD Data Source' DATA_SOURCE,
FIRST_NAME as FIRST_NM,
MIDDLE_NAME as MIDDLE_NM,
LAST_NAME as LAST_NM,
TITLE_CODE as CREDENTIALS,
SELECT DISTINCT COMMUNICATION_VALUE FROM DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='Telephone' as PHONE1,
SELECT DISTINCT COMMUNICATION_VALUE FROM DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='Cellular' as PHONE2,
SELECT DISTINCT COMMUNICATION_VALUE FROM DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='E-mail' as EMAIL1,
SELECT DISTINCT COMMUNICATION_VALUE FROM DLA_PROFESSIONAL_A WHERE COMMUNICATION_TYPE_CODE='Fax' as FAX1,
FROM DLA_PROFESSIONAL_A

All the data is in one table (DLA_PROFESSIONAL_A)

[gandolf989]

You are describing a basic pivot query. Either do the query the 8i/9i way...

http://www.adp-gmbh.ch/ora/sql/examples/pivot.html

Or look into using analytics.

SELECT deptno,
       MAX(sys_connect_by_path(ename, ' ' )) scbp
  FROM (SELECT deptno, ename,
               row_number() over
                   (PARITION BY deptno
                        ORDER BY ename) rn
          FROM emp )
         START WITH rn = 1
       CONNECT BY PRIOR rn  = rn - 1
           AND prior deptno = deptno
 GROUP BY deptno
 ORDER BY deptno;