Line break or Single row to multiple rows

1

Dear all,
I need a sample select statement query for Line break(Single Cell value split in to Multiple Rows).
Example,
Input:
ID Comment
1 Test1 Test2 Test3

Output:
1 Test1
1 Test2
1 Test3

Thanks,

2

The ability to pivot/unpivot is not currently supported today but is something we are considering for our roadmap.

3

This works, but there is also a bug…

SELECT Id, flatten(comment) AS comment
FROM (
  SELECT distinct Id, regexp_split(comment, '\Q \E', 'ALL', 10) AS comment
  FROM (
    select 1 as "Id", 'test1 test2 test3' as "comment"
  ) nested_0
) nested_0

The SQL using JDBC gives back correct results…

Id comment
1 test1
1 test2
1 test3

For some reason the Dremio UI gives back

Id comment
1 test1
1 test2
1 test3
1 test1
1 test2
1 test3
1 test1
1 test2
1 test3
1 test1
1 test2
1 test3
1 test1
1 test2
1 test3
1 test1
1 test2
1 test3
4

The Dremio UI fixes itself (3 rows showing) if you change Preview to Run and if you switch back to Preview it stays at 3 rows… Bug with initial Preview

5

Hi David,

By Using Flatten it is Splitting in all Double quotes, my requirement is to split at every ANL

Sample:
["\nANL;1;DSC-C121;1;1;V",“CAP”,“DSC-C121”,">",“0.05V;;FAIL(+/-);0.000000e+00;0.000000e+00;0.000000e+00;;;1\r\nANL;1;”]

here i need
ANL;1;DSC-C121;1;1;V",“CAP”,“DSC-C121”,">","0.05V;;FAIL(+/-);0.000000e+00;0.000000e+00;0.000000e+00;;;1 as First line

ANL as Second line

Thanks.

6

Just change the REGEX to split a string into array by “\n” instead of " "

SELECT flatten(raw_column) AS raw_column
FROM (
SELECT regexp_split(raw_column, ‘\Q\n\E’, ‘ALL’, 10) AS raw_column
FROM (
select ‘\nANL;1;DSC-C121;1;1;V",“CAP”,“DSC-C121”,">",“0.05V;;FAIL(+/-);0.000000e+00;0.000000e+00;0.000000e+00;;;1\r\nANL;1;”’ as “raw_column”
) nested_0
) nested_0

raw_column

ANL;1;DSC-C121;1;1;V","CAP","DSC-C121",">","0.05V;;FAIL(+/-);0.000000e+00;0.000000e+00;0.000000e+00;;;1\r
ANL;1;"
7

Thanks David,

yes it is working Fine.

i did like this
SELECT row_key, flatten(regexp_split(ANL, ‘\Q
\E’, ‘ALL’, 100000)) AS ANL

1 Like