Avatar of sam2929
sam2929
 asked on

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.
Oracle Database

Avatar of undefined
Last Comment
Pawan Kumar

8/22/2022 - Mon
Pawan Kumar

Please find the tested solution below -

Table creation and data generation

CREATE TABLE classes
(
	 Id  INT 
	,class1 DECIMAL(10,3)
);

INSERT INTO classes VALUES (123,.001);
INSERT INTO classes VALUES (123,.002);

Open in new window


SOLUTION

SELECT Id , MAX(CASE WHEN rnk = 1 THEN Class1 END) Class1
		  , MAX(CASE WHEN rnk = 2 THEN Class1 END) Class2
		  , MAX(CASE WHEN rnk = 3 THEN Class1 END) Class3
		  , MAX(CASE WHEN rnk = 4 THEN Class1 END) Class4
		  , MAX(CASE WHEN rnk = 5 THEN Class1 END) Class5
		  , MAX(CASE WHEN rnk = 6 THEN Class1 END) Class6
		  , MAX(CASE WHEN rnk = 7 THEN Class1 END) Class7
		  , MAX(CASE WHEN rnk = 8 THEN Class1 END) Class8
		  , MAX(CASE WHEN rnk = 9 THEN Class1 END) Class9
		  , MAX(CASE WHEN rnk = 10 THEN Class1 END) Class10
		  , MAX(CASE WHEN rnk = 11 THEN Class1 END) Class11
		  , MAX(CASE WHEN rnk = 12 THEN Class1 END) Class12
		  , MAX(CASE WHEN rnk = 13 THEN Class1 END) Class13
		  , MAX(CASE WHEN rnk = 14 THEN Class1 END) Class14
		  , MAX(CASE WHEN rnk = 15 THEN Class1 END) Class15
		  , MAX(CASE WHEN rnk = 16 THEN Class1 END) Class16
		  , MAX(CASE WHEN rnk = 17 THEN Class1 END) Class17
		  , MAX(CASE WHEN rnk = 18 THEN Class1 END) Class18
		  , MAX(CASE WHEN rnk = 19 THEN Class1 END) Class19
		  , MAX(CASE WHEN rnk = 20 THEN Class1 END) Class20
		  , MAX(CASE WHEN rnk = 21 THEN Class1 END) Class21
		  , MAX(CASE WHEN rnk = 22 THEN Class1 END) Class22
		  , MAX(CASE WHEN rnk = 23 THEN Class1 END) Class23
		  , MAX(CASE WHEN rnk = 24 THEN Class1 END) Class24
		  , MAX(CASE WHEN rnk = 25 THEN Class1 END) Class25
		  , MAX(CASE WHEN rnk = 26 THEN Class1 END) Class26
		  , MAX(CASE WHEN rnk = 27 THEN Class1 END) Class27
		  , MAX(CASE WHEN rnk = 28 THEN Class1 END) Class28
		  , MAX(CASE WHEN rnk = 29 THEN Class1 END) Class29
		  , MAX(CASE WHEN rnk = 30 THEN Class1 END) Class30
		  , MAX(CASE WHEN rnk = 31 THEN Class1 END) Class31
		  , MAX(CASE WHEN rnk = 32 THEN Class1 END) Class32
		  , MAX(CASE WHEN rnk = 33 THEN Class1 END) Class33
		  , MAX(CASE WHEN rnk = 34 THEN Class1 END) Class34
		  , MAX(CASE WHEN rnk = 35 THEN Class1 END) Class35
		  , MAX(CASE WHEN rnk = 36 THEN Class1 END) Class36
		  , MAX(CASE WHEN rnk = 37 THEN Class1 END) Class37
		  , MAX(CASE WHEN rnk = 38 THEN Class1 END) Class38
		  , MAX(CASE WHEN rnk = 39 THEN Class1 END) Class39
		  , MAX(CASE WHEN rnk = 40 THEN Class1 END) Class40
		  , MAX(CASE WHEN rnk = 41 THEN Class1 END) Class41
		  , MAX(CASE WHEN rnk = 42 THEN Class1 END) Class42
		  , MAX(CASE WHEN rnk = 43 THEN Class1 END) Class43
		  , MAX(CASE WHEN rnk = 44 THEN Class1 END) Class44
		  , MAX(CASE WHEN rnk = 45 THEN Class1 END) Class45
		  , MAX(CASE WHEN rnk = 46 THEN Class1 END) Class46
		  , MAX(CASE WHEN rnk = 47 THEN Class1 END) Class47
		  , MAX(CASE WHEN rnk = 48 THEN Class1 END) Class48
		  , MAX(CASE WHEN rnk = 49 THEN Class1 END) Class49
		  , MAX(CASE WHEN rnk = 49 THEN Class1 END) Class50			
FROM 
(
	SELECT Id,Class1 , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Class1) rnk FROM classes
)i
GROUP BY ID;

Open in new window


OUTPUT

|  ID | CLASS1 | CLASS2 | CLASS3 | CLASS4 | CLASS5 | CLASS6 | CLASS7 | CLASS8 | CLASS9 | CLASS10 | CLASS11 | CLASS12 | CLASS13 | CLASS14 | CLASS15 | CLASS16 | CLASS17 | CLASS18 | CLASS19 | CLASS20 | CLASS21 | CLASS22 | CLASS23 | CLASS24 | CLASS25 | CLASS26 | CLASS27 | CLASS28 | CLASS29 | CLASS30 | CLASS31 | CLASS32 | CLASS33 | CLASS34 | CLASS35 | CLASS36 | CLASS37 | CLASS38 | CLASS39 | CLASS40 | CLASS41 | CLASS42 | CLASS43 | CLASS44 | CLASS45 | CLASS46 | CLASS47 | CLASS48 | CLASS49 | CLASS50 |
|-----|--------|--------|--------|--------|--------|--------|--------|--------|--------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|---------|
| 123 |  0.001 |  0.002 | (null) | (null) | (null) | (null) | (null) | (null) | (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |  (null) |

Open in new window

awking00

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?
sam2929

ASKER
Yes they will be same and need will be ongoing
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
sam2929

ASKER
Pawan I need to compare two tables if data in table a then try to check all columns in tableb to find matching data
awking00

What version of Oracle are you using?
awking00

Also, is the data type number or varchar2?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sam2929

ASKER
Oracle 11 datatype varchar2
ASKER CERTIFIED SOLUTION
awking00

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

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.
Pawan Kumar

@Author - We can use UNPIVOT and JOIN method to get the required data.

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)\\

Open in new window

SOLUTION

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
\\

Open in new window

OUTPUT

  	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

Open in new window


Note - Tested on http://rextester.com/l/oracle_online_compiler
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck