Adding new data in columns, from another table - SQL ORACLE

Question

I have a table with some records

MASTER TABLE

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  2   |   2014-01-06       |   30  |
x------x--------------------x-------x

I have another table with parameters of this record (ID)

TABLE2

x------x--------------------x------------x
| Id   |      Parameter        |   Value |
x------x--------------------x------------x
|  1   |   Humidty             |   10    |
|  1   |   Temperature         |   30    |
|  2   |   Humidty             |   50    |
|  2   |   Temperature         |   40    |
x------x--------------------x------------x

As result I want this: (combine based on ID)

Result table

x------x--------------------x-------------------------x
| Id   |      Period        |   Humidty | Temperature |
x------x--------------------x-------------------------x
|  1   |   2014-01-13       |   10      | 30          |
|  2   |   2014-01-06       |   50      | 40          |
x------x--------------------x-------------------------x

How Can I do something like that? Inner join will not work I think.

Converting rows to columns is called PIVOT. Search around for it. — George Menoutis
– George Menoutis, Commented Apr 9, 2019 at 12:26
Please do not tag questions with multiple databases. [oracle] and [sql-server] are different platforms with different syntaxes. All you are doing is wasting the time of people who provide solutions you cannot use. — APC
– APC, Commented Apr 9, 2019 at 12:44
This is a question which occurs in assorted variations very commonly on this site. There are various different solutions. Which one might be best suited to your case depends upon your answer to the following question: does your real table2 (and desired result) have a fixed number of attributes which are always present for all entries in Master table? — APC
– APC, Commented Apr 9, 2019 at 12:50

forpas · Accepted Answer · 2019-04-09 12:41:25Z

4

Join the tables and use conditional aggregation with case to extract the 2 columns:

select t1.id, t1.period,
  max(case when t2.parameter = 'Humidty' then t2.value end) Humidty,
  max(case when t2.parameter = 'Temperature' then t2.value end) Temperature
from mastertable t1 inner join table2 t2
on t2.id = t1.id
group by t1.id, t1.period

edited Apr 9, 2019 at 12:41

answered Apr 9, 2019 at 12:30

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StepUp · Accepted Answer · 2019-04-09 12:35:30Z

You can pivot:

SELECT * FROM 
(
SELECT 
  t_m.Id
, t_m.Period 
, t_2.Parameter
, t_2.Value
FROM @tbl_Master t_m
INNER JOIN @tbl_2 t_2 ON t_2.Id = t_m.Id
)AS t
PIVOT 
(
    MAX(t.Value)
    FOR t.Parameter IN ([Humidity], [Temperature])
)pvt

and sample data:

DECLARE @tbl_Master TABLE
(
   Id int,
   Period Date,
   QTY int

)

DECLARE @tbl_2 TABLE
(
   Id int,
   Parameter varchar(30),
   [Value] int

)

INSERT INTO @tbl_Master
(
    Id,
    Period,
    QTY
)
VALUES
  (1, '2014-01-13', 10)
, (2, '2014-01-06', 30)


INSERT INTO @tbl_2
(
    Id ,
   Parameter ,
   [Value] 
)
VALUES
  ( 1, 'Humidity', 10)
, ( 1, 'Temperature' , 30)
, ( 2, 'Humidity', 50)
, ( 2, 'Temperature' , 40)

OUTPUT:

Id    Period    Humidity       Temperature
1   2014-01-13    10            30
2   2014-01-06    50            40

Sreenu131 · Accepted Answer · 2019-04-09 12:37:55Z

Try this

DECLARE @Mastertable AS TABLE(Id INT,PERIOD DATE,QTY INT)
INSERT INTO @Mastertable
SELECT 1 ,'2014-01-13', 10  UNION ALL
SELECT 2 ,'2014-01-06', 30

DECLARE @Childtable AS TABLE(Id INT,Parameter  VARCHAR(100), Value INT)
INSERT INTO @Childtable
SELECT  1  ,'Humidty'             ,   10  UNION ALL
SELECT  1  ,'Temperature'         ,   30  UNION ALL
SELECT  2  ,'Humidty'             ,   50  UNION ALL
SELECT  2 , 'Temperature'          ,   40  

SELECT  Id,Period,[Humidty],[Temperature]
FROM
(
SELECT  c.Id,
        m.PERIOD,
        Parameter,
        c.Value
 FROM @Mastertable m
INNER JOIN  @Childtable c
ON m.Id = c.Id
) AS srC
pivot 
(MAX(Value) FOR Parameter IN ([Humidty],[Temperature])
) AS PVT

Result

Id  Period      Humidty Temperature
----------------------------------
1   2014-01-13    10      30
2   2014-01-06    50      40

Collectives™ on Stack Overflow

Adding new data in columns, from another table - SQL ORACLE

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related