Forum Stats

  • 3,817,360 Users
  • 2,259,322 Discussions
  • 7,893,760 Comments

Discussions

Matrix query

Arif2018
Arif2018 Member Posts: 214 Red Ribbon

i want to list our all possible values in matrix format. i have a table with following values with one specific column having distinct values (company ,org_code,site) based on which i want to group the rows values (code_part,code_part_val) as columns, i am not sure if it is possible.


[code]

CREATE TABLE POST_DATA(COMP VARCHAR2(12),CODE_PART VARCHAR2(5),ORG_CODE VARCHAR2(12), SITE VARCHAR2(5), CODE_PART_VAL VARCHAR2(20))


INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','CS','CE01','CCS1-C02');

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','ES','CE01','CES1-C02');

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','FS','CE01','CFS1-C02');

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C03','B','FS','CME01','CMES1-C03');

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','CS','CE01','CHM02');

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','ES','CE01','SER04');

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','FS','CE01','SER01');

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C03','C','FS','CME01','CMES01');


--needed output based on distinct column code_part


C02, B,C,'CS',CCS1-C02,CHM02

C02, B,C,'ES',CES1-C02,SER04

C02, B,C,'ES',CFS1-C02,SER01

C03, B,C,'FS',CMES1-C03,CMES01


[/CODE]

Tagged:

Best Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 144 Gold Badge
    Answer ✓

    Hi @Arif2018 ,

    please check, if pivot can also be a valid solution to solve your problem:

     SELECT comp,
         org_code,
         site,
         b,
         c
      FROM post_data
         PIVOT (MIN (code_part_val) FOR code_part IN ('B' AS b, 'C' AS c))
    ORDER BY comp ASC, org_code ASC, site ASC
    

    The code_part_val is presented here in the column named like the code_part it belongs to. The only problem with pivot is, that you have to know the distinct code_parts to include the into the pivot operation.

    Best regards

    Jan

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,336 Red Diamond
    Answer ✓

    I know OP tagged this 12C but on 19C we could use SQL macro to implement dynamic pivot:

    create or replace
      function part_matrix
        return varchar2
        sql_macro
        as
            v_stmt clob;
            cursor v_cur
              is
                select  distinct case dense_rank() over(order by code_part)
                                   when 1 then q'[(']'
                                   else q'[,']'
                                 end || code_part || q'[' as "]' || code_part || '"' code
                  from  post_data
                  order by 1;
        begin
            v_stmt := 'select * from post_data pivot(min(code_part_val) for code_part in ';
            for v_rec in v_cur loop
              v_stmt := v_stmt || v_rec.code;
            end loop;
            v_stmt := v_stmt || ')) order by comp,org_code,site';
            return v_stmt;
    end part_matrix;
    /
    

    Now:

    select  *
      from  table(part_matrix)
    /
    
    COMP         ORG_CODE     SITE  B                    C
    ------------ ------------ ----- -------------------- --------------------
    C02          CS           CE01  CCS1-C02             CHM02
    C02          ES           CE01  CES1-C02             SER04
    C02          FS           CE01  CFS1-C02             SER01
    C03          FS           CME01 CMES1-C03            CMES01
    
    SQL>
    

    SY.

Answers

  • Sergei Krasnoslobodtsev
    Sergei Krasnoslobodtsev Member Posts: 495 Silver Badge
    edited Aug 12, 2021 9:43AM

    The easiest way for your solution is to aggregate the values into a string.

    If you need columns(in dataset), then you need to understand in order to get a correct output, you must first determine the maximum nesting level in the dataset. This will determine the maximum number of added columns.

    In other words, you cannot produce 3 columns for one row, and 7 columns for the next row.

    May be helpful:

    listagg

    pivot and unpivot

    pivot example

    >=18c:

    How to Dynamically Change the Columns in a SQL Query

    For example(<18c):

    select 
    comp,
    listagg(code_part,',') within group (order by null) code_part,
    org_code,
    listagg(code_part_val,',') within group (order by null) code_part_val
    from post_data
    group by comp,org_code;
    
    
     
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,935 Red Diamond

    Hi, @Arif2018

    Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful!

     i have a table with following values with one specific column having distinct values (company ,org_code,site) 

    Sorry, I don't follow what you're saying here. Which one column in that table has distinct values? In the sample data you posted, none of those three columns has distinct values; in fact, the combination of those three values is not distinct (e.g., there are two different rows that have company = 'C02', org_code = 'CS' and site = 'ce01').

    --needed output based on distinct column code_part


    C02, B,C,'CS',CCS1-C02,CHM02

    ...

    Always explain how you get the desired results from the given data. What does each row of the output represent? How many columns are in the output? (It would help if you formatted the output.) What results would you want of you added this row to the existing sample data:

    INSERT INTO POST_DATA (COMP , CODE_PART, ORG_CODE, SITE,   CODE_PART_VAL )
                   VALUES ('C02', 'X',       'CS',     'CE01', 'YEE-HAW');
    

     ?

  • Arif2018
    Arif2018 Member Posts: 214 Red Ribbon

    Hi Frank,

    Basically the distinct values are combination of 4 columns company,org_code,site and code_part . i need is the following output, It is very much like transposing and grouping the values into columns of different rows.

    as @Sergei Krasnoslobodtsev displayed the output but the code_parts to be in different columns, i think i need to use his solution to split the two columns B,C etc.

    C02, B,C,'CS',CCS1-C02,CHM02

    C02, B,C,'ES',CES1-C02,SER04

    C02, B,C,'ES',CFS1-C02,SER01

    C03, B,C,'FS',CMES1-C03,CMES01

    [/CODE]

  • Jan Gorkow
    Jan Gorkow Member Posts: 144 Gold Badge
    Answer ✓

    Hi @Arif2018 ,

    please check, if pivot can also be a valid solution to solve your problem:

     SELECT comp,
         org_code,
         site,
         b,
         c
      FROM post_data
         PIVOT (MIN (code_part_val) FOR code_part IN ('B' AS b, 'C' AS c))
    ORDER BY comp ASC, org_code ASC, site ASC
    

    The code_part_val is presented here in the column named like the code_part it belongs to. The only problem with pivot is, that you have to know the distinct code_parts to include the into the pivot operation.

    Best regards

    Jan

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,336 Red Diamond
    Answer ✓

    I know OP tagged this 12C but on 19C we could use SQL macro to implement dynamic pivot:

    create or replace
      function part_matrix
        return varchar2
        sql_macro
        as
            v_stmt clob;
            cursor v_cur
              is
                select  distinct case dense_rank() over(order by code_part)
                                   when 1 then q'[(']'
                                   else q'[,']'
                                 end || code_part || q'[' as "]' || code_part || '"' code
                  from  post_data
                  order by 1;
        begin
            v_stmt := 'select * from post_data pivot(min(code_part_val) for code_part in ';
            for v_rec in v_cur loop
              v_stmt := v_stmt || v_rec.code;
            end loop;
            v_stmt := v_stmt || ')) order by comp,org_code,site';
            return v_stmt;
    end part_matrix;
    /
    

    Now:

    select  *
      from  table(part_matrix)
    /
    
    COMP         ORG_CODE     SITE  B                    C
    ------------ ------------ ----- -------------------- --------------------
    C02          CS           CE01  CCS1-C02             CHM02
    C02          ES           CE01  CES1-C02             SER04
    C02          FS           CE01  CFS1-C02             SER01
    C03          FS           CME01 CMES1-C03            CMES01
    
    SQL>
    

    SY.