SQL statement - get multiple values in one column

Question

I have an table with data like these:

  id | name | value

    12 | KREA | 3,7 

    13 | KREA | 12.6

    12 | GFR  | 2.2

    13 | GFR  | 1.7

Now I want to get the name and value of krea and gfr in one column like this:

 id | name1 | value1 | name2 | value2

    12 | KREA  | 3.7    | GFR   | 2.2

...

I have tried to make an join but it didn't work.

select rps1.id, rps1.name, rps1.value, rps2.id, rps2.name, rps2.value 
  from table1 rps1, table1 rps2 
 where rps1.name like 'KREA' 
    or rps2.name like 'GRFR' and rps1.id = rps2.id
   and crd > '17.07.2019 00:00:00' order by id

Can anyone tell me what I am doing wrong? Thanks in advance! :)

Which RDBMS? Ohh and please stop using implicit joins ... From t1, t2 ... — Ocaso Protal
– Ocaso Protal, Commented Jul 18, 2019 at 6:48

Ed Bangga · Accepted Answer · 2019-07-18 06:51:27Z

2

use this.

select
  t1.id,
  t1.name,
  t1.value,
  t2.id,
  t2.name,
  t2.value
from
  (select id, name, value from table1 where name='KREA' and crd > '17.07.2019 00:00:00') t1
join
  (select id, name, value from table1 where name='GFR' and crd > '17.07.2019 00:00:00') t2
        on t1.id = t2.id
order by
  t1.id

answered Jul 18, 2019 at 6:51

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

Comments

Popeye · Accepted Answer · 2019-07-18 08:06:53Z

0

The query which you tried is perfectly fine except a few mistakes.

rps1.name like 'KREA' 
    or rps2.name like 'GRFR' 
--> need to use AND instead of OR
--> need to use 'GFR' instead of 'GRFR' 

order by id
--> Need to use alias rps1.id

-- So your query will go like this:

select rps1.id, rps1.name, rps1.value, rps2.id, rps2.name, rps2.value 
  from table1 rps1, table1 rps2 
 where rps1.name like 'KREA' 
    AND rps2.name like 'GFR' and rps1.id = rps2.id
   and crd > '17.07.2019 00:00:00' 
   order by rps1.id

db<>fiddle demo

Cheers!!

answered Jul 18, 2019 at 8:06

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL statement - get multiple values in one column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related