How can I convert MS SQL T-SQL into Subsequent in MySQL

user_9663070 0
Viewed 41

Here I have a MS SQL query which I would like to convert into MySQL. I don't know how to deal with that.

WITH PartitionData as (
  SELECT
    TOP 10 PurchaseDate.PurchaseDate AS date,
    BatchCode,
    ROW_NUMBER() OVER( PARTITION BY PurchaseDate.PurchaseDate ORDER BY ( SELECT NULL ) ) AS RowNumber

  FROM  tblNutBatches
  INNER JOIN PurchaseDate ON PurchaseDate.PurchaseDate BETWEEN tblNutBatches.Introduction_date AND tblNutBatches.expiration_date 
  WHERE  PurchaseDate.PurchaseDate = '2004-05-01'
   
)

SELECT
  date as date,
  [1],
  [2],
  [3],
  [4],
  [5],
  [6],
  [7],
  [8],
  [9]
FROM
  (
    SELECT
      date,
      BatchCode,
      RowNumber
    FROM
      PartitionData
  )AS  DataAfterFilter 
  
  PIVOT 
  (

    MAX(BatchCode) FOR RowNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
  ) A

OUTPUT IN SQL SERVER

enter image description here

Any idea would be appreciated.

aggregate-functions pivot datetime mysql sql
1 Answers

You would typically use conditional aggregation:

SELECT date,
    MAX(CASE WHEN rn =  1 THEN BatchCode end) as BatchCode1,
    MAX(CASE WHEN rn =  2 THEN BatchCode end) as BatchCode2,
    ...
    MAX(CASE WHEN rn = 10 THEN BatchCode end) as BatchCode10
FROM (
    SELECT 
        pd.PurchaseDate AS date,
        BatchCode,
        ROW_NUMBER() OVER(PARTITION BY pd.PurchaseDate ORDER BY BatchCode) AS rn
    FROM  tblNutBatches nb
    INNER JOIN PurchaseDate pd ON pd.PurchaseDate BETWEEN nb.Introduction_date AND nb.expiration_date 
    WHERE  pd.PurchaseDate = '2004-05-01'
) t
WHERE rn <= 10
GROUP BY date

Notes:

  • if you want a consistent results, you need ORDER BY clauses in the subquery and in ROW_NUMBER() - I used BatchCode

  • do prefix BatchCode with the alias of the table it belongs to

  • the PARTITION BY clause of ROW_NUMBER() and the outer GROUP BY clause are not necessary strictly speaking, since the subquery is filtering on just one data anyway; I retained them, in case you need to remove the filtering at some point. Accordingly, I moved the top 10 filtering logic form the subquery to the outer query.

user_106767160
Related