Data concatenation issue In Azure Databricks

Source Data

Expected Results

explanation:
scenario 1: if i have 3 records for one ID along with 2 names and one savings records, we are expecting into 'Expected Results' column like below

Scenario 2:
scenario 1: if i have 2 records for one ID along with 2 names and no savings records, we are expecting into 'Expected Results' column like below

scenario 3:
if i have 2 records for one ID along with 1 names and one savings records, we are expecting into 'Expected Results' column like below

scenario 4:
if i have one records for one ID, that is only one name , we are expecting into 'Expected Results' column like below

scenario 5:
if i have one records for one ID, that is only savings or null/empty , we are expecting into 'Expected Results' column like below

Databricks SQL:

SELECT a.*, 
       CASE 
         WHEN b.Expected_results LIKE '%&%' THEN b.Expected_results
         WHEN b.Expected_results IS NULL THEN 'Not Required' 
         ELSE CONCAT(b.Expected_results, ' only') 
       END AS Expected_results
FROM Input_Table a 
LEFT JOIN (
  SELECT id, ARRAY_JOIN(COLLECT_SET(Name), ' & ') Expected_results
  FROM Input_Table
  WHERE Name <> 'Savings'
  GROUP BY id
) b ON a.id = b.id
ORDER BY id;

The inner query groups the records by ID and concatenates the names of individuals with the same ID, separated by a comma. It uses the COLLECT_SET function to collect the names into an array, and the ARRAY_JOIN function to concatenate the array elements into a string. It does not include the records where the name is "Savings" to collect set and array_join function.

The outer query joins the original table with the concatenated names(inner query), and uses a CASE statement to handle the different scenarios you mentioned. It uses a LEFT JOIN to join the original table with the subquery on the ID column. This ensures that all records from the original table are included in the result, even if there is no matching record in the subquery.

The CASE statement checks the concatenated names in the subquery and handles the different scenarios you mentioned. If the concatenated names contain a comma, it returns the concatenated names as is. If there is no concatenated name (i.e., the subquery returns NULL), it returns "Not Required". Otherwise, it concatenates the concatenated names with the string " only".

Output: