CODEWITHSUNDEEP
sqlhana
KABIL ARASAN
KABIL ARASAN

Reputation: 31

How to split multiple values from a row into separate rows?

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

Upvotes: 1

Views: 3363

Answers (2)

Charles P
Charles P

Reputation: 1

Slight tweaking of the use of the SUBSTR_REGEXPR function. I've used this just last week in Hana as a Script_View and can confirm it works. 

Begin
VAR_OUT =
    select *
    from ( select
    ST.PARAM, -- Source table id
    NT.Element_Number, -- Occurrence number within source table row
SUBSTR_REGEXPR( '([;])([^;]*)(?=;)'
IN   CONCAT(CONCAT(';',ST.VALUE),';')
     OCCURRENCE NT.Element_Number
    GROUP 2
    ) splitted      -- string piece
from 
"_SYS_BIC"."Financial_Planning_and_Analysis/ZZTPARPAR_SOURCE" as ST, -- source  table
SERIES_GENERATE_INTEGER(1, 0, 10 ) as NT -- numbers table
    ) tbl
where splitted is not null
order by PARAM, Element_Number;
End

Before and After

Upvotes: 0

Christoph G
Christoph G

Reputation: 555

  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: Split string?

Upvotes: 3

Related Questions