CODEWITHSUNDEEP
mysqlsqlsql-server
kevincicero
kevincicero

Reputation: 79

sql-server to mysql translation

I'm trying to recreate in mysql something created in MS SQL. I'm having a heck of time getting the syntax right. Does anyone know what the equivalent mysql query would be for the following: 

 create table #tmp
(id int, Ran varchar(10), Result int, ref_id int)
insert #tmp values (1,  'Object1', 4.0,  1)
insert #tmp values (2,  'Object2', 100,  1)
insert #tmp values (3,  'Object1', 6.0,  2)
insert #tmp values (4,  'Object3', 89.0, 2)

select * from #tmp

Select t.ref_id
      ,TK =  max(case when t.Ran ='Object1' then t.[Result] end)
      ,CRP=  max(case when t.Ran ='Object2' then t.[Result] end)
      ,HPT=  max(case when t.Ran = 'Object3' then t.[Result] end)
      From #tmp t
group by t.ref_id

Thank you for taking a look!

Upvotes: 0

Views: 64

Answers (2)

Farooque
Farooque

Reputation: 3766

MySQL equivalent query for above sql query:

create table #tmp
(id int, Ran varchar(10), Result int, ref_id int);
insert into #tmp values (1,  'Object1', 4.0,  1);
insert into #tmp values (2,  'Object2', 100,  1);
insert into #tmp values (3,  'Object1', 6.0,  2);
insert into #tmp values (4,  'Object3', 89.0, 2);

select * from #tmp;

Select t.ref_id
      ,TK =  max(case when t.Ran ='Object1' then t.Result end)
      ,CRP=  max(case when t.Ran ='Object2' then t.Result end)
      ,HPT=  max(case when t.Ran ='Object3' then t.Result end)
      from #tmp t
group by t.ref_id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This doesn't seem difficult:

create temporary table tmp (
    id int,
    Ran varchar(10),
    Result int,
    ref_id int
);

insert into tmp(id, Ran, Result, ref_id) values (1,  'Object1', 4.0,  1);
insert into tmp(id, Ran, Result, ref_id) values (2,  'Object2', 100,  1);
insert into tmp(id, Ran, Result, ref_id) values (3,  'Object1', 6.0,  2);
insert into tmp(id, Ran, Result, ref_id) values (4,  'Object3', 89.0, 2);

select * from tmp;

Select t.ref_id,
       max(case when t.Ran ='Object1' then t.Result end) as TK,
       max(case when t.Ran ='Object2' then t.Result end) as CRP,
       max(case when t.Ran = 'Object3' then t.Result end) as HPT
From tmp t
group by t.ref_id;

Here is a pretty close SQL Fiddle.

Upvotes: 2

Related Questions