Matrix query

Arif2018 · Aug 12, 2021 2:56AM

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]

Jan Gorkow · Sep 4, 2021 9:57AM

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 · Sep 4, 2021 11:12AM

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.

Sergei Krasnoslobodtsev · Aug 12, 2021 9:17AM

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 · Aug 12, 2021 11:26AM

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 · Aug 12, 2021 12:00PM

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 · Sep 4, 2021 9:57AM

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 · Sep 4, 2021 11:12AM

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.

Forum Stats

Quick Links

Categories

Matrix query

Best Answers

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Matrix query

Best Answers

Answers