question

Hellen avatar image
Hellen asked

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
t-sqlpivot
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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`
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
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
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+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.
1 Like 1 ·
Thank you Oleg! I don't know T-SQL. Is there away to query with regular SQL? Thank you for help!
0 Likes 0 ·
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?
0 Likes 0 ·
@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.
0 Likes 0 ·
@Oleg, the CASE depends on Oracle version. I think that from Oracle 9i and up also the CASE is working.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.