Split the comma separated column into distinct rows in SAP HANA

Question

tags column in my below table is comma separated and I need to split it into distinct rows as shown below. I have seen multiple links on the forum but most of the combinations of functions doesn't work in SAP HANA. Any help would be highly appreciated.

My_Table:

+-----+--------------+------------+-------------+
| id  | parent_title | account_id |    tags     |
+-----+--------------+------------+-------------+
| 647 | title999     |         64 | 361,381,388 |
| 646 | title998     |         64 | 361,376,388 |
+-----+--------------+------------+-------------+

Required_Table

+-----+--------------+------------+------+
| id  | parent_title | account_id | tags |
+-----+--------------+------------+------+
| 647 | title999     |         64 |  361 |
| 647 | title999     |         64 |  381 |
| 647 | title999     |         64 |  388 |
| 646 | title998     |         64 |  361 |
| 646 | title998     |         64 |  376 |
| 646 | title998     |         64 |  388 |
+-----+--------------+------------+------+

Frankly, if you want to treat the tags as individual elements, don't store them in a comma-separated list. It's that simple. This is the most basic relational database design. — Bill Karwin, Jun 20 '17 at 14:28
Yes @BillKarwin you are right, but over here the data is already stored and I can't find a way to make it right — Nitin Pal, Jun 20 '17 at 14:49
Duplicate of https://stackoverflow.com/questions/44110999/how-to-split-multiple-values-from-a-row-into-separate-rows/44113101#44113101 . Check my answer. — Christoph G, Jun 20 '17 at 18:49

score 0 · Answer 1 · answered Jun 20 '17 at 14:48

This will work, need to create 2 temp tables . It is always a good idea to store values individualy as Bill Suggested. I assume you have 3 digits followed by coma in the string and compiled this code. This will work on the sample data u have given.

create table #temp
( id int, parent_title varchar(100), account_id int, tags varchar(max))
insert into #temp
values ( '647','title999','64', '361,381,388');

insert into #temp
values ( '647','title999','64', '361,376,388');

create table #temp2
( id int, parent_title varchar(100), account_id int, tags varchar(max)); 


insert #temp2 (id,parent_title,account_id,tags)
select id, parent_title,account_id, LEFT(tags,3) tags   from #temp;

insert #temp2 (id,parent_title,account_id,tags)
select id, parent_title,account_id,  right (tags,3) tags    from #temp ;


insert #temp2 (id,parent_title,account_id,tags)
select id, parent_title,account_id,  left( substring( tags,5,6),3) tags   from #temp ;

 select * from #temp2

 drop table #temp ;
 drop table #temp2

There is no limit on the number of tags and their digits – Nitin Pal Jun 20 '17 at 15:33 — Nitin Pal, Jun 20 '17 at 15:33

score 0 · Answer 2 · edited Jan 05 '18 at 17:54

Try

SELECT id, parent_title, account_id, STRING_AGG(tags,',' ORDER BY tags ) AS tags
from your_Table
group by id, parent_title, account_id
order by 1 desc

Result

| id    | parent_title | account_id | tags               |

| 647 | title999       | 64               | 361,381,388 |

| 646 | title998       | 64               | 361,376,388 |

Split the comma separated column into distinct rows in SAP HANA

2 Answers2