Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sql query for combining different status for item order id

I have three columns

1.order_item_id where datatype is STRING,
2.delivered_date_time where datatype is TIMESTAMP ,
3.status where datatype is string ,

Write a sql query to combine the different status of particular order_item_id separated by semi-colon and first delivered_date_time of order_item_id should come first and
second delivered_date_time of order_item_id should come second and so on.

wrote a query with STRING_AGG and GROUP_CONCAT function but it is giving me below error –

Query validation failed

Syntax Validation: [PARSING] Error while parsing query line 3:25: no viable alternative at input 'SELECTn order_item_id,nSTRING_AGG(status, '; ' ORDER'

SQL Query:-

SELECT
  order_item_id,
 GROUP_CONCAT(status ORDER BY delivered_date_time ASC SEPARATOR '; ') as  status_list
//STRING_AGG(status, '; ' ORDER BY delivered_date_time ASC) AS status_list
FROM
  Order
GROUP BY
  order_item_id;

Can anyone please help me solving this issue?

Eg-

order_ITEM_ID   status  
1            UNDELIVERED  
1            IN_PROGRESS  
1            REJECTED  
1            ready_to_ship  
2            APPROVED  
2            on_hold  
2            null

output:-

  order_ITEM_ID status  
1   UNDELIVERED;IN_PROGRESS;REJECTED ;  ready_to_ship  
2   APPROVED;on_hold;null
Tags:

2

Answers


  1. 0

    If you are on SQL Server 2017 or higher then you have a choice of using the STRING_AGG function as follows:

    STRING_AGG(status, '; ') 
  WITHIN GROUP (ORDER BY delivered_date_time ASC) AS status_list

    Before SQL Server 2017, We need to use STUFF function.

    Refer STRING_AGG documentation for more details on the function

    Login or Signup to reply.
  2. 0

    It’s unclear whether the database in question is SQL Server or MySQL.

    Using SQL Server :

    You can use STRING_AGG function to concatenate the statuses and order them based on the delivered_date_time.

    SELECT 
    ID,
    STRING_AGG(status, ';') WITHIN GROUP (ORDER BY delivered_date_time) AS status_combined
FROM 
    order_status
GROUP BY 
    ID

    See this db<>fiddle.

    Using MySql :

    In MySQL by using the GROUP_CONCAT function to combine the statuses for each ID and ordering the statuses based on their delivered_date_time.

    SELECT ID, GROUP_CONCAT(status ORDER BY delivered_date_time SEPARATOR ';') AS status
FROM order_status
GROUP BY ID;

    See this db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search