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 Go to next message
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 Go to previous messageGo to next message
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 #677706 is a reply to message #677704] Tue, 08 October 2019 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Rows into Columns [message #677707 is a reply to message #677706] Tue, 08 October 2019 06:53 Go to previous messageGo to next message
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 #677708 is a reply to message #677707] Tue, 08 October 2019 07:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The data model is totally & fatally flawed.
Within B_TABLE when you have multiple rows, there is no way to determine which row is supposed to be associated with any other row in this table.
Between A_TABLE & B_TABLE there is nothing to associate specific rows in A_TABLE with any specific rows in B_TABLE.

The populated sample data should contain details for at least 3 different objects to provide sufficient spread to better show real world environment.
Re: Rows into Columns [message #677711 is a reply to message #677707] Tue, 08 October 2019 10:00 Go to previous messageGo to next message
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 #677724 is a reply to message #677711] Wed, 09 October 2019 05:18 Go to previous messageGo to next message
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
Senior Member
Thankful and grateful to you Genius Experts, i think this query will work, as you suggested to define the relationship, can you please guide me how to define the relation ship?? will be grateful to you.

Again thanks a lot, great help.
Re: Rows into Columns [message #677740 is a reply to message #677724] Thu, 10 October 2019 04:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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



  • Attachment: worksheet.csv
    (Size: 5.52KB, Downloaded 1955 times)

[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 Go to previous messageGo to next message
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 #677856 is a reply to message #677820] Wed, 16 October 2019 04:44 Go to previous messageGo to next message
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
Senior Member
I am grateful to you, for detailed explanation for solution, really we appreciate your all support and help. Got it, now i understand your procedure and follow the same.

Thankful to all the great expert for all support, help and guidance.


best regards,

Re: Rows into Columns [message #678497 is a reply to message #677856] Mon, 09 December 2019 09:51 Go to previous message
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
Previous Topic: delete from table where one of the column is nested table is running long.
Next Topic: How to Pass number value to a varchar field
Goto Forum:
  


Current Time: Thu Mar 28 04:03:08 CDT 2024