Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: [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

    COMMUNICATION_TYPE_CODE column
    Telephone
    Telephone
    Fax
    Fax
    E-mail
    E-mail
    Cellular
    Cellular

    COMMUNICATION_VALUE column
    224-256-5689
    547-568-5642
    254-565-4526
    524-465-6542
    jones@gmail.com
    james@yahoo.com
    562-564-7854
    654-452-6897


    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
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try it like this:
    Code:
    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,
           ( 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

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    i tried that but I am getting the error

    ORA-01427: single-row subquery returns more than one row
    01427. 00000 - "single-row subquery returns more than one row"
    *Cause:
    *Action:

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    You are getting that exception because their is more than one distinct communication_value for one of the communication_type_codes.

    Try this and you will see a result row returned for each of the communication_value's:
    Code:
    with 
    phone1 as(
      select distinct communication_value
       from  dla_professional_a
      where  communication_type_code  =  'Telephone' ),
    phone2 as(
      select distinct communication_value
       from  dla_professional_a
      where  communication_type_code  =  'Cellular'  ),
    email1 as(
      select distinct communication_value
       from  dla_professional_a
      where  communication_type_code  =  'E-mail'    ),
    fax1 as(
      select distinct communication_value
       from  dla_professional_a
      where  communication_type_code  =  'Fax'       )
    select 'IMD Data Source' as data_source,
           dpa.first_name as first_nm,
           dpa.middle_name as middle_nm,
           dpa.last_name as last_nm,
           dpa.title_code as credentials,
           p1.communication_value as phone1,
           p2.communication_value as phone2,
           e1.communication_value as email1,
           f1.communication_value as fax1
     from  dla_professional_a dpa left outer join phone1 p1 on dpa.communication_type_code  =  p1.communication_type_code
                                  left outer join phone2 p2 on dpa.communication_type_code  =  p2.communication_type_code
                                  left outer join email1 e1 on dpa.communication_type_code  =  e1.communication_type_code
                                  left outer join fax1   f1 on dpa.communication_type_code  =  f1.communication_type_code

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select .....
    decode(COMMUNICATION_TYPE_CODE,'Telephone',COMMUNI CATION_VALUE,null) telephone,
    decode(COMMUNICATION_TYPE_CODE,'Cellular',COMMUNIC ATION_VALUE,null) CELLULAR, ....
    FROM dla_professional_a
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •