Home » SQL & PL/SQL » SQL & PL/SQL » Rows into Columns (2 merged) (Oracle 10G, Windows 2012R2)
Rows into Columns (2 merged) [message #677704] |
Tue, 08 October 2019 05:58 |
|
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Dear All,
We have two tables
In Table B_TABLE we have two Columns ATTRIB_A and ATTRIB_B, in these columns we have a following values
ATTRIB_A ATTRIB_B
LENGTH_A 10
WIDTH_A 50
In Table A_TABLE we have the columns same like the values of B_table Column e.g. LENGTH_A, WIDTH_A, now we want to populate the
values of ATTRIB_B in the Columns of A_TABLE.
So in LENGTH_A and WIDTH_A value should populate 10, 50 from B_Table to A_Table Column because rows in B_Table same like the Columns in A_Table
is it possible to populate??
following is the table script..
CREATE TABLE A_TABLE
(
LENGTH_A NUMBER,
HEIGHT_A NUMBER,
FORMULA VARCHAR2(50 BYTE),
RESULT NUMBER,
WIDTH_A NUMBER,
AREA_A NUMBER
)
=============================================
CREATE TABLE B_TABLE
(
ATTRIB_A VARCHAR2(100),
ATTRIB_B NUMBER
)
LENGTH_A 10
WIDTH_A 50
Thankful
|
|
|
Rows into Columns [message #677705 is a reply to message #677704] |
Tue, 08 October 2019 06:01 |
|
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Dear All,
We have two tables
In Table B_TABLE we have two Columns ATTRIB_A and ATTRIB_B, in these columns we have a following values
ATTRIB_A ATTRIB_B
LENGTH_A 10
WIDTH_A 50
In Table A_TABLE we have the columns same like the values of B_table Column e.g. LENGTH_A, WIDTH_A, now we want to populate the
values of ATTRIB_B in the Columns of A_TABLE.
So in LENGTH_A and WIDTH_A value should populate 10, 50 from B_Table to A_Table Column because rows in B_Table same like the Columns in A_Table
is it possible to populate??
following is the table script..
CREATE TABLE A_TABLE
(
LENGTH_A NUMBER,
HEIGHT_A NUMBER,
FORMULA VARCHAR2(50 BYTE),
RESULT NUMBER,
WIDTH_A NUMBER,
AREA_A NUMBER
)
=============================================
CREATE TABLE B_TABLE
(
ATTRIB_A VARCHAR2(100),
ATTRIB_B NUMBER
)
LENGTH_A 10
WIDTH_A 50
Thankful
|
|
|
|
Re: Rows into Columns [message #677707 is a reply to message #677706] |
Tue, 08 October 2019 06:53 |
|
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Sorry for Inconvenience,
Dear All,
We have two tables
In Table B_TABLE we have two Columns ATTRIB_A and ATTRIB_B, in these columns we have a following values
ATTRIB_A ATTRIB_B
LENGTH_A 10
WIDTH_A 50
In Table A_TABLE we have the columns same like the values of B_table Column e.g. LENGTH_A, WIDTH_A, now we want to populate the
values of ATTRIB_B in the Columns of A_TABLE.
So in LENGTH_A and WIDTH_A value should populate 10, 50 from B_Table to A_Table Column because rows in B_Table same like the Columns in A_Table
is it possible to populate??
following is the table script..
CREATE TABLE A_TABLE
(
LENGTH_A NUMBER,
HEIGHT_A NUMBER,
FORMULA VARCHAR2(50 BYTE),
RESULT NUMBER,
WIDTH_A NUMBER,
AREA_A NUMBER
)
=============================================
CREATE TABLE B_TABLE
(
ATTRIB_A VARCHAR2(100),
ATTRIB_B NUMBER
)
LENGTH_A 10
WIDTH_A 50
==================================================
Insert into B_TABLE
(ATTRIB_A, ATTRIB_B)
Values
(' LENGTH_A ', 10);
Insert into B_TABLE
(ATTRIB_A, ATTRIB_B)
Values
('WIDTH_A', 20);
COMMIT;
Thankful
|
|
|
|
Re: Rows into Columns [message #677711 is a reply to message #677707] |
Tue, 08 October 2019 10:00 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Generally, the required output is the result of pivot operation.
For your sample input (unique ATTRIB_A column values producing one row in A_TABLE), it is as easy as
insert into a_table(length_a, height_a, formula, result, width_a, area_a)
select max(case when trim(attrib_a) = 'LENGTH_A' then attrib_b end) length_a
, max(case when trim(attrib_a) = 'HEIGTH_A' then attrib_b end) heigth_a
, 'whatever' formula
, 0 result
, max(case when trim(attrib_a) = 'WIDTH_A' then attrib_b end) width_a
, max(case when trim(attrib_a) = 'AREA_A' then attrib_b end) area_a
from b_table;
Note the use of TRIM function as the posted ATTRIB_A value contains leading and trailing spaces.
If the ATTRIB_A contained exact values, you could get rid of its call.
As BlackSwan noted, there may be problems if there are multiple rows with the same ATTRIB_A value leading to multiple row creation to A_TABLE.
In that case, how shall rows in B_TABLE with different ATTRIB_A construct one row in A_TABLE?
There is no "default" relationship among individual rows in B_TABLE unless it is explicitly defined - e.g. by extra column having the same value for one constructed row in A_TABLE.
As this relationship is not defined in the posted test case, so it cannot be (non-deterministically) handled.
|
|
|
|
Re: Rows into Columns [message #677740 is a reply to message #677724] |
Thu, 10 October 2019 04:25 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
I actually means what I said - adding extra column to B_TABLE having the same value for one constructed row in A_TABLE
alter table b_table add(formula varchar2(50 byte));
, properly fill it - maybe primary key would be good for enforcing data sanity:
alter table b_table modify (attrib_a not null);
alter table b_table modify (formula not null);
alter table b_table add constraint b_pk primary key (formula, attrib_a);
and use it in the query instead of 'whatever' to get formula value.
In that case, you would have to add GROUP BY clause as well.
What exactly are you trying to implement?
If it is something like this: https://community.oracle.com/thread/2550268, there are many advices in this OTN thread; I would just like to highlight this one: https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
Or, are you already trapped in this kind of EAV data model? Good luck then.
|
|
|
Re: Rows into Columns [message #677764 is a reply to message #677740] |
Fri, 11 October 2019 06:22 |
|
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Thankful to all my respectable Experts for all support
Basically we have a measurement sheets attached in Excel format for your kind reference,
here in this Excel file you can find two tabs for example have 2 measurement sheets,
there column headings or attributes like
Unit Avg. Length Avg. Width Avg. Height
OR
Nos "LENGTH(Ft)" "WIDTH(Ft)" "HEIGHT(Ft)"
NOW these are column wise,
they have almost 100 attributes or Columns same as above.
now we need to make an input form but they do not want the form with 100 columns they want to enter values of these attributes in Row wise, and want to display the value in Excel format as per requirement which column they feed value should show and store in database in Column format in Table.
as we define two tables A_table and B_Table.
in A_table they will enter the values Row wise
in B_Table the system should insert the values Column wise.
Every time they have different formula for each measurement sheet.
hope you understand my request, any expert opinion how we can resolve this problem. in Excel they are making measurement sheet in different tabs, same they want but challenge is how to save their required data of 100 columns as per their format in Database...
Thankful
[Updated on: Fri, 11 October 2019 06:25] Report message to a moderator
|
|
|
Re: Rows into Columns [message #677820 is a reply to message #677764] |
Mon, 14 October 2019 02:59 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
As I have written earlier - for your sample input (unique ATTRIB_A column values producing one row in A_TABLE), the query I provided shall produce correct result.
What happened when you tried it in your procedure with setting the values of FORMULA and RESULT to the appropriate ones?
Not sure why A_TABLE must a real table. If you really think so, just make sure it is defined as temporary one (I cannot find documentation for Oracle DB version lower than 11gR2; most probably because they are desupported).
Otherwise, when calling from multiple sessions at the same time, data from other sessions would be also present in the result set - which you probably do not want.
The described procedure depends on your frontend tool (Excel?); for its precise steps including the suitable place of input data transformation, you shall investigate in its area.
|
|
|
|
Re: Rows into Columns [message #678497 is a reply to message #677856] |
Mon, 09 December 2019 09:51 |
|
ramsql
Messages: 4 Registered: August 2015 Location: chennai
|
Junior Member |
|
|
select * from b_table where attrib_a in (select column_name from all_tab_columns where table_name='A_TABLE' );
Please check this answer is useful or not
|
|
|
Goto Forum:
Current Time: Mon Jun 10 04:30:35 CDT 2024
|