Split Multiple Comma-Separated Column into Separate Rows

Split one row into multiple rows based on comma-separated string column

Use unnest on the array returned by split.

SELECT a,split_b 
FROM tbl
CROSS JOIN UNNEST(SPLIT(b,',')) AS t (split_b)

Split (explode) pandas dataframe string entry to separate rows

How about something like this:

In [55]: pd.concat([Series(row['var2'], row['var1'].split(','))              
for _, row in a.iterrows()]).reset_index()
Out[55]:
index 0
0 a 1
1 b 1
2 c 1
3 d 2
4 e 2
5 f 2

Then you just have to rename the columns

Split comma delimited cell into multiple rows, keeping Original Rows as it is?

Ok got the Simple solution.

  1. Just Split All Rows by Comma Delimiter 'using power query'. we get "After Split Rows"
  2. Then Paste All Original Rows Above "After Split Rows"
  3. Add the filter to numbers/id row & use "Sort smallest to largest"
    and Done.

We get the Expected Result with the Original Rows & Split Rows Below them, as shown in above question.

How to split comma delimited data from one column into multiple rows

If you are on SQL Server 2016 or better, you can use OPENJSON() to split up the code values instead of cumbersome string operations:

SELECT t.Employee_FullName,
Code = LTRIM(j.value),
Hours = MAX(CASE j.[key]
WHEN 0 THEN RegularTime
WHEN 1 THEN DoubleTime
WHEN 2 THEN Overtime END)
FROM dbo.MyTable AS t
CROSS APPLY OPENJSON('["' + REPLACE(t.Code,',','","') + '"]') AS j
GROUP BY t.Employee_FullName, LTRIM(j.value);
  • Example db<>fiddle

Splitting a comma separated value into separate rows in Pandas

Here is one way using explode:

df.Col1 = df.Col1.str.split(',')                                                               
df.explode('Col1')

Output:

  Col1   Col2  Col3
0 1 0.034 0.1
1 2 1.234 0.2
1 3 1.234 0.2
1 4 1.234 0.2
2 5 0.300 1.3


Related Topics



Leave a reply



Submit