Forum Stats

  • 3,817,330 Users
  • 2,259,314 Discussions
  • 7,893,751 Comments

Discussions

Categories

Matrix query

Arif2018
Arif2018 Member Posts: 214 Red Ribbon
in SQL & PL/SQL

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:
· Share on TwitterShare on Facebook

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
    image.png

    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

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook

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;
    image.png 
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
Frank Kulash 
     
    

Frank Kulash 

Member, Moderator Posts: 41,934 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');

     ?
     
    

      · Share on TwitterShare on Facebook
     
    
 
    

    

    • 

  • 

    

    
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]
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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

    

    

image.png

    

    

    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
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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.
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    •