I'm trying to get the get the fields values as comma separated values in single cell for each ID

I am using on Azure Databricks (SQL), I know we can achieve this on traditional SQL using for xml path .. but don't see this function available in Azure Databricks. Do we have any other way we can achieve the below expected results?

Sample data as below

ID  User      Department
1   User1     Admin
2   User1     Accounts
3   User2     Finance
4   User3     Sales
5   User3     Finance

I am looking for SQL query on databricks which give me below output

ID  User      Department
1   User1     Admin,Accounts
2   User2     Finance
3   User3     Sales, Finance
select distinct t.[user],
  STUFF((SELECT distinct ', ' + t1.department
         from yourtable t1
         where t.[user] = t1.[user]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtable t;

I am looking for similar script which works on databricks..

1 Answers

Idleguys On

I think found the solution... it can achieved using one of the spark function.. concat_ws(', ',collect_set( col_name )) .. thank you