How to get output in below format

Question

1.00/5 (1 vote)

See more:

The data in below format with pl sql script from two tables Product and category

product category count total_count
iPhone   iphone1    2     6
         iphone2    3
         iphone3    1
Samsung  samsung1   4     8
         samsung2   2
         samsung3   2

What I have tried:

I know to how to join and grouping. but in this format not getting a idea. Please advise

Posted 13-Aug-24 3:58am

varsh12

Add a Solution

Comments

Pete O'Hanlon 13-Aug-24 11:50am

It would help if you actually showed us what your tables look like, as well as what the data is. This post requires us to make a lot of assumptions, which you could easily resolve by providing more detail, especially as the total_count appears to be a count category entries rolled up to the top level. That really looks like you are mixing concerns there - getting the query to retrieve data in a format that looks tabular with summary information.

varsh12 13-Aug-24 13:51pm

Below is the table structure:
Product table: prod_id,product name(like iphone/samsung),category_id
Category table: cat_id,cat_name(like iphone1/samsung1).
## cat_id is mapped with category_id in product table. in product table many category id falls under product name. SO each product have many category.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Accepted Answer · 2024-08-13T11:05:00

As you are trying to do this in pl/sql, you should be able to something based on the following.

“sql”

DECLARE
    v_product VARCHAR2(100);
    v_total_count NUMBER := 0;
    
    CURSOR product_cursor IS
        SELECT DISTINCT product_name
        FROM Product
        ORDER BY product_name;
    
    CURSOR category_cursor(p_product VARCHAR2) IS
        SELECT c.cat_name, COUNT(*) as count
        FROM Product p
        JOIN Category c ON p.category_id = c.cat_id
        WHERE p.product_name = p_product
        GROUP BY c.cat_name
        ORDER BY c.cat_name;
BEGIN
    
    FOR product_rec IN product_cursor LOOP
        v_product := product_rec.product_name;
        v_total_count := 0;
        
        FOR category_rec IN category_cursor(v_product) LOOP
            IF v_total_count = 0 THEN
                DBMS_OUTPUT.PUT_LINE(RPAD(v_product, 8) || ' ' || 
                                     RPAD(category_rec.cat_name, 9) || ' ' || 
                                     LPAD(TO_CHAR(category_rec.count), 5));
            ELSE
                DBMS_OUTPUT.PUT_LINE(RPAD(' ', 8) || ' ' || 
                                     RPAD(category_rec.cat_name, 9) || ' ' || 
                                     LPAD(TO_CHAR(category_rec.count), 5));
            END IF;
            v_total_count := v_total_count + category_rec.count;
        END LOOP;
        

        DBMS_OUTPUT.PUT_LINE(RPAD(' ', 24) || LPAD(TO_CHAR(v_total_count), 11));
    END LOOP;
END;
/

It’s not an ideal solution because it ignores the power of set based operations but it fulfils what you’re asking for,