CODEWITHSUNDEEP
sqlselect
oracledba
oracledba

Reputation: 13

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

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

PROFESSIONAL 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
[email protected]
[email protected]
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 PROFESSIONAL WHERE  COMMUNICATION_TYPE_CODE='Telephone' as PHONE1,
SELECT DISTINCT COMMUNICATION_VALUE FROM PROFESSIONAL WHERE COMMUNICATION_TYPE_CODE='Cellular' as PHONE2,
SELECT DISTINCT COMMUNICATION_VALUE FROM PROFESSIONAL WHERE COMMUNICATION_TYPE_CODE='E-mail' as EMAIL1,
SELECT DISTINCT COMMUNICATION_VALUE FROM PROFESSIONAL WHERE   COMMUNICATION_TYPE_CODE='Fax' as FAX1,
FROM PROFESSIONAL

All the data is in one table (PROFESSIONAL)

Upvotes: 0

Views: 3352

Answers (3)

HeatfanJohn
HeatfanJohn

Reputation: 7323

A column in a relational table can only contain one value. This means that based on the schema you have provided (column names) each entry of the PROFESSIONAL table can only have one value for COMMUNICATION_CODE and one value for COMMUNICATION_VALUE. In relational database terminology this is said to be First Normal Form.

That being said you could have multiple entries in the PROFESSIONAL table each of which would have the same name information but unique COMMUNICATION_VALUE and COMMUNICAITON_CODE data. However, this violates another relational design consideration which is to minimize duplication of data.

My recommendation is to place COMMUNICATION_VALUE and COMMUNICATION_CODE into a separate table and then use the primary key of the PROFESSIONAL table as a secondary key of this new COMMUNICATION table to be able to select all of the COMMUNICATION TABLE values for each entry of the PROFESSIONAL table.

Does that make sense?

Upvotes: 0

Sean
Sean

Reputation: 15144

That's not possible.

I'm assuming you have something like:

JIM | SMITH | TELEPHONE | 224-256-5689 
JIM | SMITH | TELEPHONE | 547-568-5642

How must a single (distinct) JIM SMITH row know which of the communication_values to return when there is more than one?

You either get >1 row, or you need to select TOP 1 or some other criteria to return the single value you require.

Maybe you're wanting something like this as your nested select:

(SELECT TOP 1 COMMUNICATION_VALUE FROM PROFESSIONAL P_INNER WHERE  COMMUNICATION_TYPE_CODE='Telephone' AND P_INNER.<field/s> = P_OUTER.<field/s> ORDER BY <some_field>)

EDIT

The something like this?

CASE WHEN COMMUNICATION_TYPE_CODE='Telephone' THEN COMMUNICATION_VALUE END as PHONE1,
CASE WHEN COMMUNICATION_TYPE_CODE='Cellular' THEN COMMUNICATION_VALUE END as PHONE2,
CASE WHEN COMMUNICATION_TYPE_CODE='E-mail' THEN COMMUNICATION_VALUE END as EMAIL1,
CASE WHEN COMMUNICATION_TYPE_CODE='Fax' THEN COMMUNICATION_VALUE END as FAX1,

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

What you are doing is rather complicated. You are trying to order the contact information independently. SQL does not exactly support this.

Instead, you need to add a "row number" column, which is called seqnum in the code below. The query uses this information to combine the contact informatioon onto the appropriate row. I find the union and group method easier for this (the alternative is full outer joins).

select p.<whatever>, phone1, phone2, EmailContacts, fax
from Professional p left outer join
     (select ProfessionalId, seqnum,
             MAX(Phone1) as Phone1, MAX(Phone2) as Phone2, MAX(Email) as Email, MAX(Fax) as fax
      from ((select ProfessionalId, COMMUNICATION_VALUE as Phone1,
                    NULL as Phone2, NULL as Email, NULL as Fax
                    row_number() over (partition by ProfessionalId order by ProfessionalId) as seqnum
             from (select distinct ProfessionalId, COMMUNICATION_VALUE,
                   from Professional p
                   where COMMUNICATION_TYPE_CODE = 'Telephone'
                  )
            ) union all
            (select ProfessionalId, NULL, COMMUNICATION_VALUE, NULL, NULL
                    row_number() over (partition by ProfessionalId order by ProfessionalId) as seqnum
             from (select distinct ProfessionalId, COMMUNICATION_VALUE,
                   from Professional p
                   where COMMUNICATION_TYPE_CODE = 'Cellular'
                  )
            ) union all
            (select ProfessionalId, NULL, NULL, COMMUNICATION_VALUE, NULL
                    row_number() over (partition by ProfessionalId order by ProfessionalId) as seqnum
             from (select distinct ProfessionalId, COMMUNICATION_VALUE,
                   from Professional p
                   where COMMUNICATION_TYPE_CODE = 'E-mail'
                  )
            ) union all
            (select ProfessionalId, NULL, NULL, NULL, COMMUNICATION_VALUE,
                    row_number() over (partition by ProfessionalId order by ProfessionalId) as seqnum
             from (select distinct ProfessionalId, COMMUNICATION_VALUE,
                   from Professional p
                   where COMMUNICATION_TYPE_CODE = 'Fax'
                  )
            )
           )
     group by ProfessionalId, seqnum
    ) c
    on c.ProfessionalId = p.ProfessionalId

This query assumes there is a ProfessionalId on each row, to identify each person.

Upvotes: 1

Related Questions