syntax change from mySQL 5.5 to SQL Server 2005

Question

I have the following code written in mySQL 5.5 version.

SELECT ss.ParcelID,ss.ParcelName,sh.Qty1,ShapeID1, sh.Qty2,sh.ShapeID2, sh.Qty3, sh.ShapeID3, sh.Qty4,sh.ShapeID4,
       MAX(case when ShapeID = 'EM' then Cng_InQty end) as EM_Qty,
       MAX(case when ShapeID = 'EM' then Cng_InWeight end) as EM_Weight,
       MAX(case when ShapeID = 'PR' then Cng_InQty end) as PR_Qty,
       MAX(case when ShapeID = 'PR' then Cng_InWeight end) as PR_Weight,
       MAX(case when ShapeID = 'AS' then Cng_InQty end) as AS_Qty,
       MAX(case when ShapeID = 'AS' then Cng_InWeight end) as AS_Weight
FROM sql_history ss
join sql_stock sh
WHERE ss.ParcelID = sh.ParcelID
GROUP BY ss.ParcelID;

I need the same query to be executed in SQL Server 2005 but i can not write the query with the correct syntax.

See also the `ONLY_FULL_GROUP_BY` Sql Mode: http://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by — StuartLC, Jan 06 '14 at 08:13

score 2 · Answer 1 · answered Jan 06 '14 at 08:03

In SQL Server, you can't use a column that is not in a GROUP BY in the SELECT statement with out an aggregate function. Either use an aggregate function with it or add it to the GROUP BY:

SELECT 
  ss.ParcelID,
  ss.ParcelName,
  sh.Qty1,
  ShapeID1, 
  sh.Qty2,
  sh.ShapeID2, 
  sh.Qty3, 
  sh.ShapeID3, 
  sh.Qty4,
  sh.ShapeID4,
  MAX(case when ShapeID = 'EM' then Cng_InQty end) as EM_Qty,
  MAX(case when ShapeID = 'EM' then Cng_InWeight end) as EM_Weight,
  MAX(case when ShapeID = 'PR' then Cng_InQty end) as PR_Qty,
  MAX(case when ShapeID = 'PR' then Cng_InWeight end) as PR_Weight,
  MAX(case when ShapeID = 'AS' then Cng_InQty end) as AS_Qty,
  MAX(case when ShapeID = 'AS' then Cng_InWeight end) as AS_Weight
FROM sql_history ss
join sql_stock sh ON ss.ParcelID = sh.ParcelID
GROUP BY ss.ParcelID,
  ss.ParcelName,
  sh.Qty1,
  ShapeID1, 
  sh.Qty2,
  sh.ShapeID2, 
  sh.Qty3, 
  sh.ShapeID3, 
  sh.Qty4,
  sh.ShapeID4;

Mysql, however, permits that, in this case it selects an arbitrary values for those columns.

syntax change from mySQL 5.5 to SQL Server 2005

1 Answers1