1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Display similar columns together in matrix report

Discussion in 'SQL PL/SQL' started by shaina, Apr 19, 2016.

  1. shaina

    shaina Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    india
    I have a query like this

    SELECT TO_CHAR(A.JOB_DATE , 'MON-YYYY') S_MON , NULL T_MON , TO_CHAR(A.JOB_DATE,'YYYY') MM ,
    C.SECTION_CODE SECTION , B.ITEM_CODE ITEM_CODE , SUM(B.MASTER_QUANTITY ) SALES

    FROM TRANSACTION_MASTER A , TRANSACTION_DETAIL B , ITEM_SECTION C , ITEM D

    WHERE A.TRANSACTION_NUMBER = B.TRANSACTION_NUMBER
    AND a.job_date between add_months (to_date( :ST_DATE, 'DD-MON-YYYY'),-3) and ADD_MONTHS(to_date( :ST_DATE, 'DD-MON-YYYY'),-1)
    AND A.TRANSACTION_CODE = B.TRANSACTION_CODE
    AND B.ITEM_CODE = D.ITEM_CODE
    AND C.SECTION_CODE = D.SECTION_CODE
    AND A.TRANSACTION_CODE IN (2,4)
    AND C.SECTION_CODE BETWEEN :ST_CODE AND :END_CODE
    AND B.ITEM_CODE = 'NMV0170'

    GROUP BY TO_CHAR(A.JOB_DATE , 'MON-YYYY') , TO_CHAR(A.JOB_DATE,'YYYY') ,C.SECTION_CODE , B.ITEM_CODE

    UNION

    SELECT NULL S_MON , TO_CHAR(A.JOB_DATE,'MON-YYYY') T_MON, TO_CHAR(A.JOB_DATE,'MM-YYYY') MM,
    D.SECTION_CODE SECTION , C.ITEM_CODE ITEM_CODE , SUM(B.AMOUNT) TARGET

    FROM TARGET_MASTER_Q_NEW A , TARGET_DETAIL_Q_NEW B, ITEM C , ITEM_SECTION D

    WHERE A.TRANSACTION_NUMBER = B.TRANSACTION_NUMBER
    AND B.ITEM_CODE = C.ITEM_CODE
    AND C.SECTION_CODE = D.SECTION_CODE
    AND A.JOB_DATE BETWEEN :ST_DATE AND '31-DEC-2016'
    AND D.SECTION_CODE BETWEEN :ST_CODE AND :END_CODE
    AND C.ITEM_CODE = 'NMV0170'

    GROUP BY TO_CHAR(A.JOB_DATE,'MON-YYYY'), TO_CHAR(A.JOB_DATE,'MM-YYYY') , C.ITEM_CODE , D.SECTION_CODE ;


    this is a matrix report and it gives a result like this

    item | x y x y x y

    but i want the result to be like this

    item | x x x x | y y y y

    I want all X to display together as a separate column and all Y to display as another separate column

    how do i structure the query to get that result. Please Help me.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Please ,provide here scripts : create of table,fillng table.
     
  3. shaina

    shaina Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    india
    Hi,
    Sorry, but i can't get you which script you are asking for?
    Are you asking for the scripts used to create all the tables used in the query?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Well, there are no problems.
    Please,specify what is X and Y in your query.

    what version of your oracle ?

    perhaps, necessary for you of aggregation of rows ?

    for example :
    Code (SQL):
    DROP TABLE sample_data;
    CREATE TABLE sample_data
    AS
    SELECT
        MOD(level,2) id,
        trunc(dbms_random.VALUE(-3,3)) x ,
        trunc(dbms_random.VALUE(3,7)) y
    FROM
        dual
    CONNECT BY level <= 10;

    SELECT
        id,
        rtrim(xmlagg(xmlelement("X",s.x||',')).EXTRACT('/X/text()'),',') list_x,
        rtrim(xmlagg(xmlelement("Y",s.y||',')).EXTRACT('/Y/text()'),',') list_y
    FROM
        sample_data s
    GROUP BY id;


    if you don't need aggregation of rows(here max level is set as 5)

    Code (SQL):
    WITH
        step1 AS
        (
            SELECT
                id,
                s.x,
                s.y,
                ROW_NUMBER() OVER (partition BY s.id ORDER BY s.x)  rn    
            FROM
                sample_data s
        )
    SELECT
        st1.id,
        MAX(CASE WHEN st1.rn = 1 THEN st1.x END ) x1,
        MAX(CASE WHEN st1.rn = 2 THEN st1.x END ) x2,
        MAX(CASE WHEN st1.rn = 3 THEN st1.x END ) x3,
        MAX(CASE WHEN st1.rn = 4 THEN st1.x END ) x4,          
        MAX(CASE WHEN st1.rn = 5 THEN st1.x END ) x5,              
     
        MAX(CASE WHEN st1.rn = 1 THEN st1.y END ) y1,
        MAX(CASE WHEN st1.rn = 2 THEN st1.y END ) y2,
        MAX(CASE WHEN st1.rn = 3 THEN st1.y END ) y3,
        MAX(CASE WHEN st1.rn = 4 THEN st1.y END ) y4,          
        MAX(CASE WHEN st1.rn = 5 THEN st1.y END ) y5
    FROM
        step1 st1      
    GROUP BY
        st1.id;
     
    Last edited: Apr 19, 2016
  5. shaina

    shaina Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    india
    Thanx for the idea Krasnoslobodtsev_si . Helped a lot ..!!