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
case when EMAIL_CODE = 'HOME' then
from TBL A where EMAIL_CODE in ( 'work' , 'home' )
group by ID,EMAIL_CODE, EMAIL_ADDRESS ) mail
where B.ID = mail.ID
I want to display them in 3 column as below:
Please help! Thank you in advance!Hellen
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:
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):
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