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..