CODEWITHSUNDEEP
sqloracle-databasepivot
patricK
patricK

Reputation: 1105

Oracle group value as a column header

I have a table where the column value I want to show as a new column in select. Next images show what I want.

I tried to use "pivot" but it not worked. Is it possible to do with pivot function?

My query:

 select m.tmarti  as line, dsmdcp as column, vlmdmn as valuee    
 from TABLE01 m 
--pivot (count(vlmdmn for dsmdcp in ('Cintura', 'Quadril', 'Busto'))
    join TABLE02 t on t.tmarti = m.tmarti
 where cdarti = 2026397 
 order by t.seqtam, dsmdcp;

my select

What I trying to do

Upvotes: 1

Views: 189

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

You can use Conditional Aggregation in such a way that to create a select statement string to pivot those columns dynamically within a stored function which returns a value in SYS_REFCURSOR type such as

CREATE OR REPLACE FUNCTION Get_Pivoted_Values RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767); 
BEGIN
  SELECT LISTAGG( 'MAX( CASE WHEN column = '''||column||''' THEN valuee END )  
              AS  "'||column||'"' , ',' ) WITHIN GROUP ( ORDER BY dsmdcp )
    INTO v_cols
    FROM ( SELECT DISTINCT dsmdcp
             FROM table02 );
           
  v_sql :=
  'SELECT tmarti,'|| v_cols ||
  '  FROM
     (
      SELECT t1.tmarti AS line, dsmdcp AS column, vlmdmn AS valuee
        FROM table01 t1
        JOIN table02 t2
          ON t2.tmarti = t1.tmarti
     )
    GROUP BY tmarti';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

The function might be invoked from the SQL Developer's console as

SQL> DECLARE
    result SYS_REFCURSOR;
BEGIN
   :result := Get_Pivoted_Values;
END;
/

SQL> PRINT result;

Upvotes: 2

Related Questions