Solved

how to write case statement or loop statement in simple sql query for same product with multiple sub products??

Posted on 2016-09-01
4
32 Views
Last Modified: 2016-10-22
how to write case statement or loop statement in simple sql query for same product with multiple sub products??


eg If I have product computer then sub products are like laptop,desktop,harddisk,ram etc

table is like productno | productsubno | productdesc|
                         1                         2                              laptop
                          1                          2                           desktop
                         1                         2                            harddisk                     like this...

and I want output like  

product            productsubcode   desc1       desc2          desc3            etc...
1                                         2             laptop        desktop       hardisk      


Please help in simple sql query...
0
Comment
Question by:newdeveloper india
4 Comments
 
LVL 4

Accepted Solution

by:
Abhimanyu Suri earned 500 total points
ID: 41779802
SELECT productno,
         productsubno,
         LISTAGG (productdesc, ',') WITHIN GROUP (ORDER BY productdesc)
    FROM TABLE_X
GROUP BY productno, productsubno

o/p :

1 2 laptop,desktop,hardisk

You can achieve output without commas by modifying
" LISTAGG (productdesc, ',') WITHIN GROUP (ORDER BY productdesc)"  TO
" LISTAGG (productdesc, '      ') WITHIN GROUP (ORDER BY productdesc)"

You can publish it as a csv by using

  SELECT productno||','||
         productsubno||','||
         LISTAGG (productdesc, ',') WITHIN GROUP (ORDER BY productdesc)
    FROM TABLE_X
GROUP BY productno, productsubno

1,2,laptop,desktop,hardisk
0
 

Author Comment

by:newdeveloper india
ID: 41785837
error found like this..

 FROM keyword not found where expected
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41785860
which query did you use? did you transcribe it accurately?

i.e. it is hard for use to know what that error relates to. Please copy/paste the EXACT query you used and the EXACT error message.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Parametric query in oracle 6 51
query returning everything 11 91
su - oracle could not open session 6 50
PL/SQL Display based on value 4 19
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now