Home » SQL & PL/SQL » SQL & PL/SQL » SQL single row/multiple rows combination
SQL single row/multiple rows combination [message #204665] Tue, 21 November 2006 10:39 Go to next message
orcacle
Messages: 6
Registered: November 2006
Junior Member
Hi there,

I would like to know is it possible to do the following using only pure SQL without writing any other PL or Oracle procedure..

Say, I have two tables, one with uniqueId and unique product code, the other with product description in different languages

example:

table A
Id Code
1 AAA
2 BBB

table B
Id description language-code
1 productA english
1 produitA french
1 produktA german


Now, I would like to display product description list for product AAA in a single row:

Id Code english french german
1 AAA productA produitA produktA

Thanks in advance

Re: SQL single row/multiple rows combination [message #204671 is a reply to message #204665] Tue, 21 November 2006 11:11 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Are there always a set number of languages or is this also a variable ?
Re: SQL single row/multiple rows combination [message #204672 is a reply to message #204671] Tue, 21 November 2006 11:19 Go to previous messageGo to next message
orcacle
Messages: 6
Registered: November 2006
Junior Member
there are 3 languages with their distinct language codes (E, F, D) and the tableA id = tableB id

the explanation is not the top one, if you need more details let me know

[Updated on: Tue, 21 November 2006 11:21]

Report message to a moderator

Re: SQL single row/multiple rows combination [message #204673 is a reply to message #204672] Tue, 21 November 2006 11:24 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
VERY HOKEY but it works.


 select product_id, product_code,
max(decode(column#,1,product_desc)) English,
              max(decode(column#,2,product_desc)) French ,max(decode(column#,3,product_desc)) German
 from ( select a.product_id, a.product_code, product_desc
             , row_number() OVER
                (partition by a.product_id
                 order by language_code ) column#
         from product_table a, language_table b where a.product_id = b.product_id )
group by product_id ,product_code 

[Updated on: Tue, 21 November 2006 11:37]

Report message to a moderator

Re: SQL single row/multiple rows combination [message #218050 is a reply to message #204673] Tue, 06 February 2007 10:13 Go to previous messageGo to next message
orcacle
Messages: 6
Registered: November 2006
Junior Member
(Well, it was some time ago) Thank you very much for your solution MarcL, it did work quite fine all this time.
Unfortunately, now I have to deal with a scenario where description is not present in all possible (3) language codes and my resulting data gets shifted (ea no english description, so the french one becomes english and german french.

Is there anything to do about?

Thanks in advance..
Re: SQL single row/multiple rows combination [message #218064 is a reply to message #204665] Tue, 06 February 2007 11:23 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


What version of database are you on ?

Srini
Re: SQL single row/multiple rows combination [message #218086 is a reply to message #218064] Tue, 06 February 2007 14:51 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
SQL> select * from table_a;

        ID CODE
---------- ----
         1 AAA
         2 BBB

SQL> select * from table_b;

        ID DESCRIPTION LANGUAGE_CODE
---------- ----------- -------------
         1 Product A   English
         1 Produkt A   German

SQL> 
SQL> SELECT a.id
  2        ,a.code
  3        ,MAX(decode(b.language_code
  4                   ,'English'
  5                   ,b.description)) english
  6        ,MAX(decode(b.language_code
  7                   ,'French'
  8                   ,b.description)) french
  9        ,MAX(decode(b.language_code
 10                   ,'German'
 11                   ,b.description)) german
 12  FROM   table_a a
 13        ,table_b b
 14  WHERE  a.id = b.id
 15  GROUP  BY a.id
 16           ,a.code
 17  /

        ID CODE ENGLISH    FRENCH     GERMAN
---------- ---- ---------- ---------- ----------
         1 AAA  Product A             Produkt A
icon14.gif  Re: SQL single row/multiple rows combination [message #218185 is a reply to message #218086] Wed, 07 February 2007 02:40 Go to previous message
orcacle
Messages: 6
Registered: November 2006
Junior Member
perfect, thanks a lot...I'll try to learn from it and not bother you anymore
Previous Topic: View Compressed Packages
Next Topic: What are PL/SQL tables??
Goto Forum:
  


Current Time: Tue Dec 06 02:20:33 CST 2016

Total time taken to generate the page: 0.10127 seconds