DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Jun 2012
    Posts
    1

    [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)

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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
  •  


Click Here to Expand Forum to Full Width