Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Unpivot data
Hi,
I have table a
Id class1
123. 001
123. 002
Then table B
Id. Class1 class2 class3
123. 002. 001 002
Table b has like 50 class1 till class 50
I don’t want to do or query is it easy way to join two tables and see if table a column1 data is in table b column 1 to 49.
I have table a
Id class1
123. 001
123. 002
Then table B
Id. Class1 class2 class3
123. 002. 001 002
Table b has like 50 class1 till class 50
I don’t want to do or query is it easy way to join two tables and see if table a column1 data is in table b column 1 to 49.
Who is Participating?
Experts Exchange Solution brought to you by ConnectWise
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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.
Are all class columns in tableB the same type as the Class1 column in tableA ? Will this be a one time effort or an ongoing need?
Pawan I need to compare two tables if data in table a then try to check all columns in tableb to find matching data
with cte as(
select id, class1 as class from b union all
select id, class2 as class from b union all
select id, class3 as class from b union all
...
select id, class50 as class from b)
select id, class1 from a
intersect
select id, class from cte;
select id, class1 as class from b union all
select id, class2 as class from b union all
select id, class3 as class from b union all
...
select id, class50 as class from b)
select id, class1 from a
intersect
select id, class from cte;
Experts Exchange Solution brought to you by ConnectWise
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
what are the results you are expecting?
You may need to provide more sample data in order to post a meaningful result set.
if you're just looking for the table A data that also exists in table B, the "exists" clause should work nicely
WITH
tablea
AS
(SELECT 123 id, '001' class1 FROM DUAL
UNION ALL
SELECT 123, '002' FROM DUAL),
tableb AS (SELECT 123 id, '002' class1, '001' class2, '002' class3 FROM DUAL)
SELECT *
FROM tablea a
WHERE EXISTS
(SELECT NULL
FROM tableb b
WHERE a.id = b.id AND a.class1 IN (b.class1, b.class2, b.class3))
If that's not what you're looking for, please illustrate with a representative sample set and expected results.
You may need to provide more sample data in order to post a meaningful result set.
if you're just looking for the table A data that also exists in table B, the "exists" clause should work nicely
WITH
tablea
AS
(SELECT 123 id, '001' class1 FROM DUAL
UNION ALL
SELECT 123, '002' FROM DUAL),
tableb AS (SELECT 123 id, '002' class1, '001' class2, '002' class3 FROM DUAL)
SELECT *
FROM tablea a
WHERE EXISTS
(SELECT NULL
FROM tableb b
WHERE a.id = b.id AND a.class1 IN (b.class1, b.class2, b.class3))
If that's not what you're looking for, please illustrate with a representative sample set and expected results.
@Author - We can use UNPIVOT and JOIN method to get the required data.
Table Creation and data generation.
Note - Tested on http://rextester.com/l/oracle_online_compiler
Table Creation and data generation.
CREATE TABLE MyClass
(
Id INTEGER
,Class1 DECIMAL(10,5)
,class2 DECIMAL(10,5)
,class3 DECIMAL(10,5)
)
\\
INSERT INTO MyClass VALUES(123,0.002,0.001,0.002)
\\
CREATE TABLE classes
(
Id INT
,class1 DECIMAL(10,3)
)
\\
INSERT INTO classes VALUES (123,.001)\\
INSERT INTO classes VALUES (123,.002)\\
WITH CTE
AS
(
SELECT Id,Class1,Class2,Class3,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Id) rnk FROM MyClass
)
,CTE1 AS
(
SELECT Id,Class FROM CTE
UNPIVOT
(
class
FOR r
IN (Class1, Class2, Class3)
)
)
SELECT * FROM CTE1 c INNER JOIN classes c1
ON c.Class = c1.class1
\\
ID CLASS ID CLASS1
1 123 0,002 123 0,002
2 123 0,001 123 0,001
3 123 0,002 123 0,002
Note - Tested on http://rextester.com/l/oracle_online_compiler
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.
All Courses
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by ConnectWise
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
Table creation and data generation
Open in new window
SOLUTION
Open in new window
OUTPUT
Open in new window