CODEWITHSUNDEEP
phpmysqlsqldatabaseinner-join
user2883071
user2883071

Reputation: 980

How can I display data from the same table side by side

Say I have 1 table in mysql (called get) like this:

ID S Num
00 1 506
00 2 620
01 1 562
01 2 564
02 1 548
02 2 484
03 1 488
03 2 895

I am trying to get it in this format:

ID S1  S2
00 506 620
01 562 564
02 548 484
03 488 895

So far I have this, but it gives me an error:

select id,d.S1,c.S2 from 
(select S as S1 from get where S=1)d inner join  
(select s as S2 from get where S=2)c using (id);

I am still not too sure about joins, but this seems to make sense.

EDIT: S can have only 1 value at times, in these times, this value will be S1

Upvotes: 2

Views: 2477

Answers (2)

Taryn
Taryn

Reputation: 247850

You can use a self-join, in other words you can join on your table twice similar to the way you started. Since you stated that S=1 will always exist, then you can use the following query:

select t1.id,
  t1.num S1,
  t2.num S2
from yourtable t1
left join yourtable t2
  on t1.id = t2.id
  and t2.s = 2
where t1.s = 1;

See SQL Fiddle with Demo. Using a LEFT JOIN on your table will return all rows with the value of S=1 even if S=2 doesn't exist in your table.

You could also use an aggregate function with a CASE expression to get the result:

select
  id,
  sum(case when s = 1 then num end) S1,
  sum(case when s = 2 then num end) S2
from yourtable
group by id;

See SQL Fiddle with Demo

Upvotes: 8

user2989408
user2989408

Reputation: 3137

Though a JOIN may not be the best way to go about it, you can achieve the desired result by

SELECT t1.id, t.Num [S1], t2.Num [S2]
FROM Table t1
LEFT JOIN Table t2 ON t1.id = t2.id AND t1.s <> t2.s

Also refer to PIVOT in case there may be other values in the S column of your table.

EDIT: Changed the INNER JOIN to a LEFT JOIN after seeing the comment that said that there may be id's with no S = 2.

Upvotes: 0

Related Questions