将逗号分隔的数据库项目转换为隔离的行 [英] Convert comma seperated db items to isolated rows

问题描述

我有以下表格数据。.表格名称为类别

I have got the following table datas.. Table name is category

| ID             | Data                     | 
+----------------+--------------------------+
| 1              | Apple,Orance-........... | 
| 2              | Apple,Grapes-........... | 
| 3              | Juice,Apple,Cucumber-... |

是否可以通过任何方式将上面的逗号分隔值转换为下面的单个行的值？

Is there by any means that I could convert the above comma seperated values to that of inividual rows as below?

| ID          | SplitData
+-------------+-------------------
| 1           | Apple
| 2           | Orange
| 3           | Grapes
| 4           | Juice
| 5           | Cucumber

我使用Postgres for db和Jersey检索数据，

Im using postgres for db and Jersey to retrieve data,

推荐答案

在 unnest（）中使用 string_to_array（） ：

with a_table(id, data) as (
    values
        (1, 'Apple,Orance'),
        (2, 'Apple,Grapes'),
        (3, 'Juice,Apple,Cucumber')
)

select id, elem
from a_table,
lateral unnest(string_to_array(data, ',')) elem

 id |   elem   
----+----------
  1 | Apple
  1 | Orance
  2 | Apple
  2 | Grapes
  3 | Juice
  3 | Apple
  3 | Cucumber
(7 rows)

使用区别以获取不同的元素：

Use distinct to get distinct elements:

with a_table(id, data) as (
    values
        (1, 'Apple,Orance'),
        (2, 'Apple,Grapes'),
        (3, 'Juice,Apple,Cucumber')
)

select distinct on (elem) elem
from a_table,
lateral unnest(string_to_array(data, ',')) elem

   elem   
----------
 Apple
 Cucumber
 Grapes
 Juice
 Orance
(5 rows)    

这篇关于将逗号分隔的数据库项目转换为隔离的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！