-
[SQL] Select Distinct Values from one Column and Output SQL to Multiple Columns
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)
-
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.
Code:
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|