Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Row to Column Conversion for Tabletype data

Hi All,

I have two tabletype arrays like below.

tabtype_config_option_ft.extend;
tabtype_config_option_ft(1) := 'COLOR';
tabtype_config_option_ft.extend;
tabtype_config_option_ft(2) := 'WIDTH';
tabtype_config_option_ft.extend;
tabtype_config_option_ft(3) := 'LENGTH';
tabtype_config_option_ft.extend;
tabtype_config_option_ft(4) := 'SIZE';            


tabtype_config_options.extend;
tabtype_config_options(1) := 'RED';
tabtype_config_options.extend;
tabtype_config_options(2) := '1.23';
tabtype_config_options.extend;
tabtype_config_options(3) := '4.56';
tabtype_config_options.extend;
tabtype_config_options(4) := '13';

Open in new window


My requirement is to convert this data from row to column. The expected result should be:

COLOR	WIDTH	LENGTH	SIZE
RED	1.23	4.56	13

Open in new window


Is it possible to perform the row to column conversion?

Thanks
0
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 3
  • 2
2 Solutions
 
MikeOM_DBACommented:
This is not even SQL!
It's simple array manipulation, what have you tried?
0
 
Suriyaraj_SudalaiappanAuthor Commented:
Hi Mike,

My requirement is, I have one stored procedure with two input parameters like tabtype_config_option_ft and tabtype_config_options. I have to compare this user passed values with some of the SQL queries which is used in the procedure. Since the input data is coming as row type data I am planning to convert that into column and use it in the SQL queries and other calculations.

Usually we can do this row to column conversion if the data is stored in the tables. But here the data is stored in array's.

Thanks
0
 
Suriyaraj_SudalaiappanAuthor Commented:
Assume that I have table data like below

OPTION_FT        OPTION_VAL
COLOR	          RED
WIDTH	          1.23
LENGTH	          4.56
SIZE	                  13

Open in new window


Now i want to convert from to column like below.

COLOR	WIDTH	LENGTH	SIZE
RED	1.23	4.56	13

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
MikeOM_DBACommented:
. . . But here the data is stored in array's.
And what is so difficult about transposing an array?
Arrays are arrays, even in PL/SQL.
0
 
dbmullenCommented:
you want to PIVOT:

select *
from my_table
pivot (max(option_val) for (option_ft) in (  
'COLOR' as col_color,
'WIDTH' as col_width,  
'LENGTH' as col_length,
'SIZE'   as col_size))
0
 
MikeOM_DBACommented:
One way:
SQL> CREATE OR REPLACE TYPE Tabtype IS VARRAY (100) OF VARCHAR2 (40);
  2  /

Type created.

SQL> SET SERVEROUT ON SIZE 1000000;
SQL> DECLARE
  2     Tabtype_Config_Option_Ft Tabtype := Tabtype ();
  3     Tabtype_Config_Options Tabtype := Tabtype ();
  4     I              PLS_INTEGER;
  5     J              PLS_INTEGER;
  6  BEGIN
  7     Tabtype_Config_Option_Ft.EXTEND;
  8     Tabtype_Config_Option_Ft ( 1) := 'COLOR';
  9     Tabtype_Config_Option_Ft.EXTEND;
 10     Tabtype_Config_Option_Ft ( 2) := 'WIDTH';
 11     Tabtype_Config_Option_Ft.EXTEND;
 12     Tabtype_Config_Option_Ft ( 3) := 'LENGTH';
 13     Tabtype_Config_Option_Ft.EXTEND;
 14     Tabtype_Config_Option_Ft ( 4) := 'SIZE';
 15     Tabtype_Config_Options.EXTEND;
 16     Tabtype_Config_Options ( 1) := 'RED';
 17     Tabtype_Config_Options.EXTEND;
 18     Tabtype_Config_Options ( 2) := '1.23';
 19     Tabtype_Config_Options.EXTEND;
 20     Tabtype_Config_Options ( 3) := '4.56';
 21     Tabtype_Config_Options.EXTEND;
 22     Tabtype_Config_Options ( 4) := '13';
 23
 24     FOR I IN 1 .. Tabtype_Config_Option_Ft.COUNT
 25     LOOP
 26        DBMS_OUTPUT.Put ( Tabtype_Config_Option_Ft ( I) || CHR ( 9));
 27     END LOOP;
 28
 29     DBMS_OUTPUT.New_Line;
 30
 31     FOR I IN 1 .. Tabtype_Config_Option_Ft.COUNT
 32     LOOP
 33        DBMS_OUTPUT.Put ( Tabtype_Config_Options ( I) || CHR ( 9));
 34     END LOOP;
 35
 36     DBMS_OUTPUT.New_Line;
 37  END;
 38  /
COLOR   WIDTH   LENGTH  SIZE
RED     1.23    4.56    13

PL/SQL procedure successfully completed.

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now