CODEWITHSUNDEEP
sqloracle-database
LPK
LPK

Reputation: 536

Insert Values In Columns From Columns From A Different Table

I have the following table :

|   Cod  |  SKA  |   SKB |   SKC  |
|   AGE  |   1   |   10  |   100  |
|   AUC  |   2   |   11  |   101  |
|   BER  |   3   |   12  |   102  |
|   AGE  |   4   |   13  |   103  |
|   AUC  |   5   |   14  |   104  |
|   BER  |   6   |   15  |   105  |

And I want to add those columns in an other table to have something like this :

|   Cod  |  SKA  |   SKB |   SKC  |
|   AGE  |   1   |   10  |   100  |
|   AGE  |   1   |   11  |   101  |
|   AGE  |   1   |   12  |   102  |
|   AGE  |   1   |   13  |   103  |
|   AGE  |   1   |   14  |   104  |
|   AUC  |   1   |   15  |   105  |
|   AUC  |   2   |   10  |   100  |
|   AUC  |   2   |   11  |   101  |
|   AUC  |   2   |   12  |   102  |
|   AUC  |   2   |   13  |   103  |
|   AUC  |   2   |   14  |   104  |
|   AUC  |   2   |   15  |   105  |
|   ...  |   ... |  ...  |   ...  |
|   ...  |   ... |  ...  |   ...  |
|   ...  |   ... |  ...  |   ...  |

So every Cod, SKB and SKC according to one SKA and then according to the second etc. And this for all the SK columns,

How can I have this result ? With a join ?

EDIT

Thanks for your comment and answers I'll try a cross join !

Upvotes: 2

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you want cross join to get all combinations of the four columns:

select cod.cod, a.ska, b.skb, c.skc
from (select distinct cod from t) cod cross join
     (select distinct ska from t) a cross join
     (select distinct skb from t) b cross join
     (select distinct skc from t) c ;

You may need to adjust this query to meet your needs. Your text describes one thing, but the sample data is slightly different. I would expect AGE/2 to be in the results.

Upvotes: 1

RToyo
RToyo

Reputation: 2877

You can achieve this by joining your table to itself with a cross join. However, because your Cod column has repeated values, you may want to select the distinct rows; otherwise you'll have duplicates.

select distinct
    t1.Cod, 
    t2.SKA, t2.SKB, t2.SKC
from
    mytable t1
    cross join mytable t2

See an example in this SQL Fiddle.

Upvotes: 1

Related Questions