如何将一行中的多个值拆分为单独的行? [英] How to split multiple values from a row into separate rows?

问题描述

我想在 sap hana sql中将一行中的多个值拆分为单独的行. 表格:

I want to split multiple values from a row into a separate row in sap hana sql. table :

 id  name
 1   kabil,arasan

但是我想要这样的输出:

but I want an output like this:

id  name
1   kabil  
1   arasan

推荐答案

1. 尝试避免在列中使用类似csv的数据，例如在ETL过程中进行规范化
2. 您可以使用一个过程，请参阅[ HANA:拆分字符串?

3. ，无需执行任何过程，就可以使用以下SQL(限制:它假定csv字段中的值的最大数量).我只会将其用于特殊目的.

1. try to avoid csv-like data in columns, e.g. normalize during ETL process
2. you can use a procedure, see [HANA: Split string?

3. without a procedure you can use the following SQL (restriction: it assumes a maximum number of values in your csv-field). I would use it only for adhoc purposes.

CREATE COLUMN TABLE "TEST_SPLIT"(
    "SOME_KEY" VARCHAR(10),
    "CSV_STR" VARCHAR(1000) );

INSERT INTO "TEST_SPLIT" ("SOME_KEY", "CSV_STR") VALUES ('1', 'hello world');
INSERT INTO "TEST_SPLIT" ("SOME_KEY", "CSV_STR") VALUES ('2', 'one,two,three');
INSERT INTO "TEST_SPLIT" ("SOME_KEY", "CSV_STR") VALUES ('3', NULL);

Select * from 
( select "SOME_KEY" "KEY", "ELEMENT_NUMBER" "ORD", 
       SUBSTR_REGEXPR('(?<=^|,)([^,]*)(?=,|$)' IN "TEST_SPLIT"."CSV_STR" OCCURRENCE "SERIES"."ELEMENT_NUMBER" GROUP 1) "VAL"
  from "TEST_SPLIT",
       SERIES_GENERATE_INTEGER(1, 1, 10 ) "SERIES" -- replace 10 with your max. number of values in CSV-Field
)
where "VAL" is not null
order by "KEY", "ORD"

[1]: HANA:拆分字符串?

这篇关于如何将一行中的多个值拆分为单独的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！