x

How to display 1 column in 2 fields on the report

Hi!

I have 2 table A & B link by ID as below. It is Oracle database. I use Toad to extract data. I just learn SQL and don't know T-SQL. I tried to write case statement but it doesn't work the way I want. These following case statement only display 1 email for a ID.

select distinct ID,

case when EMAIL_CODE = 'work' then

       EMAIL_ADDRESS 
       else 
       null 
       end work_EMAIL_ADDRESS, 

case when EMAIL_CODE = 'HOME' then

       EMAIL_ADDRESS 
       else 
       null 
       end HOME_EMAIL_ADDRESS 
from TBL B,
( select ID , EMAIL_CODE, EMAIL_ADDRESS

from TBL A where EMAIL_CODE in ( 'work' , 'home' )

group by ID,EMAIL_CODE, EMAIL_ADDRESS ) mail

where B.ID = mail.ID

Tbl A

     ID   Email_address   Email_code
    1111  K@work.com       work
    1111  K@home.com       home
    2222  G@home.com       home
    2222  G@work.com       work
    3333  J@work.com       work
    4444  Z@school.com     school

Tbl B

     ID
    1111
    2222
    3333

I want to display them in 3 column as below:

ID      Email I      Email II
1111    K@work.com   K@home.com
2222    G@work.com   G@home.com
3333    J@work.com 

Please help! Thank you in advance!

Hellen
more ▼

asked Jul 26, 2011 at 07:39 PM in Default

Hellen gravatar image

Hellen
21 1 1 2

is there ever a situation where you would have three emails for a given ID in Tbl A and how would you do with it?

1111 K@Holiday.com
Jul 26, 2011 at 07:45 PM KenJ
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If 3 columns are static (meaning that no account has more than 2 email addresses then you can use the static PIVOT but in order to use it you first need to come up with the common data for all accounts. Row numbering partitioned by the ID would be a good choice. Here is the sample:

select
    pvt.ID, pvt.[1] as [Email I], pvt.[2] as [Email II]
    from 
    (
        select 
            ID, Email, row_number() over (partition by ID order by ID) N
            from TblB
    ) src
    pivot (max(Email) for N  in ([1], [2])) pvt;

This will work if you have SQL Server 2005 or better.

<!-- Begin Edit

For restating the query in Oracle dialect, the version is very important. As @Pavel Pawlowski mentioned in his comment, CASE is available in Oracle 9i or better, so it can be used in place of a more traditional DECODE. Below are the scripts for both newer and older versions of Oracle. I restated both as left join because I believe that it makes sense in a scenario when some records in the parent table named TBLB do not have the match in the child table TBLA, but the IDs from TBLB still need to be included in the results. Another important detail to point out that the names of the database objects should not contain spaces, the names are not case sensitive, but the column values are always case sensitive. This means that the words 'work' and 'home' should be either spelled in correct case or else the LOWER function should be applied to the column. Here are the scripts (one for each version):

/* This script should work in newer versions of Oracle, 9i or better */
SELECT
    TBLB.ID, MAX(
    CASE 
        WHEN mail.EMAIL_CODE = 'work' then mail.EMAIL_ADDRESS 
        ELSE NULL 
    END) WORK_EMAIL_ADDRESS, MAX(
    CASE 
        WHEN mail.EMAIL_CODE = 'home' then mail.EMAIL_ADDRESS 
        ELSE NULL 
    END) HOME_EMAIL_ADDRESS
    FROM TBLB LEFT OUTER JOIN TBLA AS mail
        ON TBLB.ID = mail.ID AND mail.EMAIL_CODE IN ('work', 'home')
    GROUP BY TBLB.ID;

/* This script should work in older versions of Oracle, 8i or worse */
SELECT
    TblB.ID,
    MAX(DECODE(mail.EMAIL_CODE, 'work', 
        mail.EMAIL_ADDRESS, NULL)) WORK_EMAIL_ADDRESS,
    MAX(DECODE(mail.EMAIL_CODE, 'home', 
        mail.EMAIL_ADDRESS, NULL)) HOME_EMAIL_ADDRESS
    FROM TBLB,
    ( 
        SELECT 
            ID, EMAIL_CODE, EMAIL_ADDRESS
            FROM TBLA WHERE EMAIL_CODE IN ('work', 'home')
    ) mail
    WHERE TBLB.ID = mail.ID(+)
    GROUP BY TBLB.ID; 

So the only problem with the original script in question was that the GROUP BY was included more columns than it deserved and it was also in the wrong place.

End Edit -->

Hope this helps,

Oleg
more ▼

answered Jul 26, 2011 at 08:17 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

+1 Also if there could be more emails per account but you know there is a maximum of the emails then you can go again the static approach and specify the maximum number of email columns. The ones not used will have null.
Jul 26, 2011 at 11:45 PM Pavel Pawlowski
Thank you Oleg! I don't know T-SQL. Is there away to query with regular SQL? Thank you for help!
Jul 27, 2011 at 05:48 PM Hellen
T-SQL is the SQL "dialect" used by SQL Server. In this context, it is regular SQL. Are you looking specifically for a solution that will work on a non-SQL Server system?
Jul 27, 2011 at 06:14 PM KenJ
@Hellen If you work with Oracle database then CASE should not work. The equivalent of CASE in SQL Server is the function named DECODE in Oracle. Please let me know whether you need a SQL Server or Oracle dialect and I will try to restate your query.
Jul 27, 2011 at 07:46 PM Oleg
@Oleg, the CASE depends on Oracle version. I think that from Oracle 9i and up also the CASE is working.
Jul 27, 2011 at 11:39 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x82

asked: Jul 26, 2011 at 07:39 PM

Seen: 1323 times

Last Updated: Jul 27, 2011 at 05:44 PM