2

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

user2883071
  • 960
  • 1
  • 20
  • 50
  • You said you have two tables but you also showed one table data. – Mawia HL Dec 04 '13 at 17:16
  • Yes, i just edited it, its only one table, basically referencing 1 table twice, but for different information – user2883071 Dec 04 '13 at 17:17
  • Will your `Id` values always have a value for `S=1` and `S=2`? Are there cases when you will have only one value for S? – Taryn Dec 04 '13 at 17:19
  • Good point, yes there maybe times when there is only 1 value for s, thanks, didn't account for that – user2883071 Dec 04 '13 at 17:21
  • @user2883071 If that is the case, then would `s=1` always be a value? – Taryn Dec 04 '13 at 17:21
  • yes, S1 would always be there – user2883071 Dec 04 '13 at 17:22
  • 1
    This requires a pivot query, which mysql doesn't support. The workarounds to simulate make for VERY ugly queries and VERY quickly become unmaintainable. Do the transform in your client code and use a standard query to get the data. – Marc B Dec 04 '13 at 17:39
  • 1
    @MarcB `PIVOT` is syntax sugar for `SUM(CASE WHEN ... THEN 1|number ELSE 0 END)` – swasheck Dec 04 '13 at 18:14
  • @swasheck: yes, but full pivot support allows abitrary numbers of fields to be specified, WITHOUT having to hack in a per-column sum(). – Marc B Dec 04 '13 at 18:24

2 Answers2

8

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
0

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.

Community
  • 1
  • 1
user2989408
  • 3,127
  • 1
  • 17
  • 15