how to get row result to column result

I have a oracle query that I need to get the results as columns instead of rows. I wanted this so that I can write another sql to take the columns and compare them. For example if I write the query
select distinct orders from order_master where order_date bettween '01-MAR-2010' and '31-MAR-2010'
The result could be 2 -5 rows depending on the orders, then I want to take those distinct orders and look for products associated to those orders and also want them to be column result since I want to write another query to take the products and find out similarities of their structure
Query -2
select distinct products from product_master where orders in (select distinct orders from order_master)
the result here again would be
order -1  product -1
order -1  product -2
order -2  product -3
order-3  product -4
order -3 product -1

Then I want to take these distinct orders and products and write another query to see their types to find out if any constant characteristics are found for the products

select distinct types from type_master
where products in (query -2 distinct products)

How can I structure this query to get the constant characteristic types of the product.? I thought getting them as columns will be easier - but any solution that can do all the 3 queries would work for me.
select
mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mahjagAuthor Commented:
one other condition from query -1 is the query should return atleast 2 rows as orders inorder to proceed to query -2 for getting the products if less than 2 then the query should not proceed.
0
slightwv (䄆 Netminder) Commented:
You should know how to get the comma separated list form your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_25301370.html

You will need to create  some PL/SQL colde using dynamic SQL to convery the list into a value for the IN clause in the second query.

However, why go through all this trouble.  Wouldn't merging the two together give you what you want?

something like:
select distinct types from type_master
where products in (select distinct products from product_master where orders in (select distinct orders from order_master)
)
0
mahjagAuthor Commented:
I think it not simple as the query mentioned
select distinct types from type_master
where products in (select distinct products from product_master where orders in (select distinct orders from order_master)
)

I want to get distinct orders (more than 1) from the first query and then take the 2 or more orders and their corresponding products and lookup their types to get only the product and types that are same on both the orders (if the order count is 2 )

Let me know if this helps..
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
I'm pretty sure all three can be done in a single query.

If you can provide me some sample data and expected results, I'll see what I can come up with.
0
mahjagAuthor Commented:
sample data
Order master table
Order_id  Code
Ord-1       1
Ord-2        1
Ord-3       2
Ord-4     3

From this table I want only the distinct orders (more than 1) that has the same code value ( in  this case code value =1 meaning ord-1 and ord-2. Do proceed to product master only if this is true.
Product master table
Prod-1
Prod-2
Prod-3
Types Table
A
B
C
sample data showing all the 3 tables
Orders        Products                 Type
Ord-1          Prod-1                     A
Ord-2           Prod-2                     B
Ord-2           Prod-1                    A
Ord-3            prod-1                     A
Ord-3             prod-3                    C
From above I only want to pick the combination of order, product and type based on firsr query it is only ord-1 and ord-2 and having same type value = A and my expected results

Ord-1          Prod-1                     A
Ord-2           Prod-1                    A
0
slightwv (䄆 Netminder) Commented:
I don't quite  get how types links to products.

Here's what I have so far.  Can you complete the example?
drop table tab1 purge;
create table tab1(order_id varchar2(10), code number);

insert into tab1 values('Ord-1',1);
insert into tab1 values('Ord-2',1);
insert into tab1 values('Ord-3',2);
insert into tab1 values('Ord-4',3);
commit;

drop table tab2 purge;
create table tab2(order_id varchar2(10), product varchar2(10));

insert into tab2 values('Ord-1','Prod-1');
insert into tab2 values('Ord-1','Prod-2');
insert into tab2 values('Ord-2','Prod-3');
insert into tab2 values('Ord-3','Prod-1');
insert into tab2 values('Ord-3','Prod-4');
commit;

Open in new window

0
mahjagAuthor Commented:
Looks like from your example of tab1 and tab2 I want to pick the orders 1 and 2 and their corresponding products prod-1,2 and 3, different products can have same chracteristics that is indicated by the letter in the types table
Product            Types
Prod -1              A
Prod -1              C
 Prod-2               B
Prod -2               A
prod-3               C

There could be 2 scenarios here
1. Orders -1 can have multiple products Prod-1 and prod-2 and they might share the same characteristics (A,B or C) then in that case the result should be product 1 and product 2 of order 1 has same type A and that should be the result
2., Also the order-1 and order -2 has prod1 and prod 3 having the same type C and that should also reflect in the result
as order -1 Prod 1 Type = C
order 2 prod-3 has Type = C

as common types.
Hope it is clear
0
slightwv (䄆 Netminder) Commented:
Thanks for the update but I think I've confused myself.  I thought I was starting to understand until you mentioned type='C' above.  I don't see the links in the data.

I was trying to piece together the data in the original question and http:#30033675.

I think I had a typo in my dataset.

Please take the following code and make any necessary changes to show your requirements (If you need a new type/product for a requirement the example won't cover).

Given either my code as-is or any additions you add/remove, can you provide the expected results?
drop table tab1 purge;
create table tab1(order_id varchar2(10), code number);

insert into tab1 values('Ord-1',1);
insert into tab1 values('Ord-2',1);
insert into tab1 values('Ord-3',2);
insert into tab1 values('Ord-4',3);
commit;

drop table tab2 purge;
create table tab2(order_id varchar2(10), product varchar2(10));

insert into tab2 values('Ord-1','Prod-1');
insert into tab2 values('Ord-2','Prod-1');
insert into tab2 values('Ord-2','Prod-2');
insert into tab2 values('Ord-3','Prod-1');
insert into tab2 values('Ord-3','Prod-3');
commit;

drop table tab3 purge;
create table tab3(product varchar2(10), type varchar2(10));

insert into tab3 values('Prod-1','A');
insert into tab3 values('Prod-1','C');
insert into tab3 values('Prod-2','B');
insert into tab3 values('Prod-2','A');
insert into tab3 values('prod-3','C');
commit;

Open in new window

0
mahjagAuthor Commented:
expected results from the data above is

Order         Product                  Type
Ord-1         Prod-1                      A
Ord-1          Prod-2                      A
Ord-1          Prod-1                      C
Ord-2           Prod-3                     C

Since order-1 has got both prod-1 and prod-2 and Ord-2 has got prod-3 that has the same type as Prod-1
I dont know how to get this result from the query..
0
sdstuberCommented:
mahjag,
"expected results from the data above is"

which data above?  slightwv's samples in http:#30040054  ?  or some other post?
0
mahjagAuthor Commented:
I dont know what is expected from me - it is totally not clear at this point - I gave my sample data and expected result  and I am hoping I can get a sql statement to run all the 3 queries with expected result - if this not what I should expect - what is expected from me? Can you guys provide solution on what I gave - ?
0
slightwv (䄆 Netminder) Commented:
Sorry I haven't bee able to get back to you on this. I've been busy at work.

I also apologize for constantly asking more questions. I have no doubt the requirements are clear to you. At times it is difficult to impart that knowledge to others simply by typing comments into a box on a web site. Unfortunately this is one of those times.

Hopefully sdstuber can help. I'll try to get some time to see what I can do later today.
0
sdstuberCommented:
mahjag,

In your post http:#30041614  you said "expected results from the data above is"

there are several examples of sample data above.   Which sample are you referring to?
0
slightwv (䄆 Netminder) Commented:
sdstuber,

I believe it was my last post:  http:#30040054.
0
sdstuberCommented:
I believe you're probably right, but I'd like mahjag to confirm that
0
mahjagAuthor Commented:
Yes I confirm that
0
sdstuberCommented:
despite the confirmation I think there might be an error.

first   "prod-3",  is that supposed to be "Prod-3"  ?

second, how is Ord-1 gettting Prod-2 in the final result?  The tab2 table has only one relationship for Ord-1 and that's for Prod-1

or maybe I'm misunderstanding what you're trying to show.

mahjag, feel free to rename the tables and columns in slightwv's examples to match to your real tables if it will help illustrate
0
mahjagAuthor Commented:
I am using the dataset that was provided from above - yes prod-3 should be Prod-3 and also Ord-1 has both Prod-1 and Prod-2 from the insert statement below

insert into tab2 values('Ord-1','Prod-1');
insert into tab2 values('Ord-1','Prod-2');
insert into tab2 values('Ord-2','Prod-3');
insert into tab2 values('Ord-3','Prod-1');
insert into tab2 values('Ord-3','Prod-4');
0
mahjagAuthor Commented:
see my explanation as well

There could be 2 scenarios here
1. Orders -1 can have multiple products Prod-1 and prod-2 and they might share the same characteristics (A,B or C) then in that case the result should be product 1 and product 2 of order 1 has same type A and that should be the result
2., Also the order-1 and order -2 has prod1 and prod 3 having the same type C and that should also reflect in the result
as order -1 Prod 1 Type = C
order 2 prod-3 has Type = C

expected results

expected results from the data above is

Order         Product                  Type
Ord-1         Prod-1                      A
Ord-1          Prod-2                      A
Ord-1          Prod-1                      C
Ord-2           Prod-3                     C

0
slightwv (䄆 Netminder) Commented:
Let me see if this helps bridge the gap since I'm a little familiar with this.

I've been trying to grasp the concept and here's the simplified version I came up with:  

For any repeating codes in tab1, you are looking for repeating types in tab3.

The bridge is tab2.

The code 1 repeats and gives you Ord-1 and Ord-2.
This leads you to tab2 and gives you: Prod-1 and Prod-2.

The then look for repeating types in tab3 for Prod-1 and Prod-2.

I'm still confident a single statement will do this.  I just haven't had the time to work on the SQL.
0
sdstuberCommented:
Ord-3 also has Prod-1, which shares A and C.

Why is it not included in the final output?  is it because the product itself is the same? if so, are you picking which order to display and which one to skip by ordering them?  Ord-1 wins over Ord-3 because 1 < 3?  
0
sdstuberCommented:
if my guesses are correct then try this...
DROP TABLE tab1 PURGE;

CREATE TABLE tab1(order_id VARCHAR2(10), code NUMBER);

INSERT INTO tab1
VALUES ('Ord-1', 1);

INSERT INTO tab1
VALUES ('Ord-2', 1);

INSERT INTO tab1
VALUES ('Ord-3', 2);

INSERT INTO tab1
VALUES ('Ord-4', 3);

COMMIT;

DROP TABLE tab2 PURGE;

CREATE TABLE tab2(order_id VARCHAR2(10), product VARCHAR2(10));

INSERT INTO tab2
VALUES ('Ord-1', 'Prod-1');

INSERT INTO tab2
VALUES ('Ord-1', 'Prod-2');

INSERT INTO tab2
VALUES ('Ord-2', 'Prod-3');

INSERT INTO tab2
VALUES ('Ord-3', 'Prod-4');

INSERT INTO tab2
VALUES ('Ord-3', 'Prod-1');

COMMIT;

DROP TABLE tab3 PURGE;

CREATE TABLE tab3(product VARCHAR2(10), TYPE VARCHAR2(10));

INSERT INTO tab3
VALUES ('Prod-1', 'A');

INSERT INTO tab3
VALUES ('Prod-1', 'C');

INSERT INTO tab3
VALUES ('Prod-2', 'B');

INSERT INTO tab3
VALUES ('Prod-2', 'A');

INSERT INTO tab3
VALUES ('Prod-3', 'C');

COMMIT;

SELECT order_id, product, TYPE
FROM (SELECT order_id, product, TYPE, COUNT(*) OVER (PARTITION BY TYPE) cnt
      FROM (SELECT tab1.order_id,
                   tab3.product,
                   tab3.TYPE,
                   ROW_NUMBER() OVER (PARTITION BY tab3.product, tab3.TYPE ORDER BY tab1.order_id)
                       rn
            FROM tab1, tab2, tab3
            WHERE tab1.order_id = tab2.order_id AND tab2.product = tab3.product)
      WHERE rn = 1)
WHERE cnt > 1
ORDER BY type,order_id,product

Open in new window

0
sdstuberCommented:
or slightly more efficient...  (again, assuming my assumptions above are correct)
SELECT order_id, product, TYPE
FROM (SELECT tab1.order_id,
             tab3.product,
             tab3.TYPE,
             ROW_NUMBER() OVER (PARTITION BY tab3.product, tab3.TYPE ORDER BY tab1.order_id) rn,
             COUNT(DISTINCT tab3.product) OVER (PARTITION BY TYPE) cnt
      FROM tab1, tab2, tab3
      WHERE tab1.order_id = tab2.order_id AND tab2.product = tab3.product)
WHERE rn = 1 AND cnt > 1
ORDER BY TYPE, order_id, product;

Open in new window

0
zubair0Commented:
please check this qry


SELECT tab1.order_id, tab2.product, tab3.TYPE
FROM
tab1, tab2, tab3
WHERE tab1.order_id = tab2.order_id
AND tab2.product = tab3.product
ORDER BY 1,2,3;


0
mahjagAuthor Commented:
Hi Slightwv - you got my requirement correct- it is unfortunate that you dont have time to get to this -
Hi Sdstuber - Ord-3 will not be selected since from tab1 the codes needs to match to select the orders, hence Ord-3 will not be used in tab2 or tab3
0
sdstuberCommented:
I have not idea what you meant by this..."Ord-3 will not be selected since from tab1 the codes needs to match to select the orders, hence Ord-3 will not be used in tab2 or tab3"

but... the queries I posted above return the requested results have you tried either of the queries I sent?
0
mahjagAuthor Commented:
Hi sdstuber

I ran the query you posted and it brought back all the types in tab3 and not the overlapping types in tab3 that I was looking for.
SELECT order_id, product, TYPE
FROM (SELECT tab1.order_id,
             tab3.product,
             tab3.TYPE,
             ROW_NUMBER() OVER (PARTITION BY tab3.product, tab3.TYPE ORDER BY tab1.order_id) rn,
             COUNT(DISTINCT tab3.product) OVER (PARTITION BY TYPE) cnt
      FROM tab1, tab2, tab3
      WHERE tab1.order_id = tab2.order_id AND tab2.product = tab3.product)
WHERE rn = 1 AND cnt > 1
ORDER BY TYPE, order_id, product;

was there another query?
Also tab1 has got column called codes and the first query that access tab1 should get only the orders that has the same code..
0
sdstuberCommented:
mahjag,  please provide more complete sample data  and expected output to illustrate all your rules
0
mahjagAuthor Commented:
Order Master table has got orders and the corresponding codes for orders, distinct orders with same code needs to be used for the second query
Order master - Table  - columns and sample data
Order_id  Code
Ord-1       1
Ord-2        1
Ord-3        2
Ord-4        3

From the aboveI want only the distinct orders (more than 1) that has the same code value ( in  this case code value =1 meaning ord-1 and ord-2. Do proceed to product master only if this is true.
Product Order master table has got the product_id product_name ( and other characteristics of proudct columns) and order_id to tie back to first table.
you can use the insert statement for sample data

insert into tab2 values('Ord-1','Prod-1');
insert into tab2 values('Ord-2','Prod-1');
insert into tab2 values('Ord-2','Prod-2');
insert into tab2 values('Ord-3','Prod-1');
insert into tab2 values('Ord-3','Prod-3');

and from the above data  the third query need to look up types for only the orders Ord-1 and Ord-2 since Ord-3 in this case doe not have same codes from the first query

user the data for table 3 types table
insert into tab3 values('Prod-1','A');
insert into tab3 values('Prod-2','B');
insert into tab3 values('prod-3','C');

Ord-1 has got Prod-1 and Ord-2 has got both Prod-1 and Prod-2 and looking at the types the Prod-1 has type A and again the same type will be for Ord-2 having Prod-1 so in this case the final result should be
Ord-1 Prod-1 Type (= A)
Ord-2 Prod-1 Type (value = A) as result

Hope this makes it all clear - shoot me for more information..
0
sdstuberCommented:
does this do it?

I'm only processing rows from tab1 that have shared codes now
SELECT order_id, product, TYPE
FROM (SELECT tab1.order_id,
             tab3.product,
             tab3.TYPE,
             ROW_NUMBER() OVER (PARTITION BY tab3.product, tab3.TYPE ORDER BY tab1.order_id) rn,
             COUNT(DISTINCT tab3.product) OVER (PARTITION BY TYPE) cnt
      FROM (SELECT order_id, code
            FROM (SELECT order_id, code, COUNT(*) OVER (PARTITION BY code) codecnt FROM tab1)
            WHERE codecnt > 1) tab1,
           tab2,
           tab3
      WHERE tab1.order_id = tab2.order_id AND tab2.product = tab3.product)
WHERE rn = 1 AND cnt > 1
ORDER BY TYPE, order_id, product;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.