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

avatar 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.

Hope this helps,

Oleg

more ▼

answered Jul 26, 2011 at 08:17 PM

avatar image

Oleg
17.1k 3 7 28

  • 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.

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:

x1066
x103

asked: Jul 26, 2011 at 07:39 PM

Seen: 1499 times

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

Copyright 2016 Redgate Software. Privacy Policy