I am trying to use string_split() function in databricks to convert below dataframe.
Source dataframe stored as TempView in Databricks:
| ID | value |
|---|---|
| 1 | value-1,value-2,value-3 |
| 2 | value-1,value-4 |
Output needed:
| ID | value |
|---|---|
| 1 | value-1 |
| 1 | value-2 |
| 1 | value-3 |
| 3 | value-1 |
| 3 | value-4 |
I tried below code:
%sql
SELECT ID, value
FROM TempView
CROSS APPLY STRING_SPLIT(value, ',')
GROUP BY cs.PERMID, value
but I am getting Parse exception.
CodePudding user response：
There is no string_split function in Databricks SQL. But there is split function for that (doc).
Also in your case it's easier to write code using the combination of split and explode (doc) functions. Something like this:
SELECT ID, explode(split(value, ',')) FROM TempView