Is there a way to load multiple values of one column into new lines to a single row in sql

This is how my data looks,

emp_id,skills
1234,python|java|sql|R|javascript
5639,C|HTML|php|perl

This is how data need to be loaded into the table

emp_id  skills
        
       python
1234    java
        sql
        R
        perl

        C
5639    HTML
        php

Actually I have replaced | with n but its not being loaded in the next line instead its just loading by adding spaces. And I will loading data into table using python etl, so even postprocessing i can add. Any suggestions??

Answer

It’s the behaviour of a relational database, that can’t just display emp_id once per group and all its elements as one per line. Changing that way of displaying data is the front end’s privilege and responsibility, not the database’s. So do that in Python.

Having said that, Impala has the SPLIT_PART() string function, which returns the n-th token of a string separated by a separator character that you pass as an argument.

So, cross joining with a series of consecutive integers, and then applying SPLIT_PART(skills,'|',i) does what you need.

Actually, in my (never too) humble opinion, this is what you should do whenever someone throws a file with such an unhappy format at you to load into the database. Always verticalise comma/bar/semicolon/or_whatever separated lists of “values” using the technique below, and store the data verticalised.:

WITH
-- your input
input( emp_id,skills) AS (
          SELECT 1234,'python|java|sql|R|javascript'
UNION ALL SELECT 5639,'C|HTML|php|perl'
)
,
-- a big enough series of integers ..
i(i) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
)
SELECT
  emp_id
, SPLIT_PART(skills,'|',i) AS skill
FROM input
CROSS JOIN i
WHERE SPLIT_PART(skills,'|',i) <> ''
ORDER BY 
  emp_id
, i
;
-- out  emp_id |   skill    
-- out --------+------------
-- out    1234 | python
-- out    1234 | java
-- out    1234 | sql
-- out    1234 | R
-- out    1234 | javascript
-- out    5639 | C
-- out    5639 | HTML
-- out    5639 | php
-- out    5639 | perl

With two bar/comma separated columns, it may look like this:

WITH
-- your input, enhanced
input( emp_id,skills,pubs) AS (
          SELECT 1234,'python|java|sql|R|javascript','ship inn,anchor,stag'
UNION ALL SELECT 5639,'C|HTML|php|perl'             ,'black horse,crown,mitre'
)
,
-- a big enough series of integers ..
i(i) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
)
,
-- another big enough series of integers ..
j(j) AS (
  SELECT
    i AS j
  FROM i
)
SELECT
  emp_id
, i AS skill_sequence
, SPLIT_PART(skills,'|',i) AS skill
, j AS pub_sequence
, SPLIT_PART(pubs,',',j) AS pub
FROM input
CROSS JOIN i
CROSS JOIN j
WHERE SPLIT_PART(skills,'|',i) <> ''
  AND SPLIT_PART(pubs,  ',',j) <> ''
ORDER BY 
  emp_id
, i
, j
;
-- out  emp_id | skill_sequence |   skill    | pub_sequence |     pub     
-- out --------+----------------+------------+--------------+-------------
-- out    1234 |              1 | python     |            1 | ship inn
-- out    1234 |              1 | python     |            2 | anchor
-- out    1234 |              1 | python     |            3 | stag
-- out    1234 |              2 | java       |            1 | ship inn
-- out    1234 |              2 | java       |            2 | anchor
-- out    1234 |              2 | java       |            3 | stag
-- out    1234 |              3 | sql        |            1 | ship inn
-- out    1234 |              3 | sql        |            2 | anchor
-- out    1234 |              3 | sql        |            3 | stag
-- out    1234 |              4 | R          |            1 | ship inn
-- out    1234 |              4 | R          |            2 | anchor
-- out    1234 |              4 | R          |            3 | stag
-- out    1234 |              5 | javascript |            1 | ship inn
-- out    1234 |              5 | javascript |            2 | anchor
-- out    1234 |              5 | javascript |            3 | stag
-- out    5639 |              1 | C          |            1 | black horse
-- out    5639 |              1 | C          |            2 | crown
-- out    5639 |              1 | C          |            3 | mitre
-- out    5639 |              2 | HTML       |            1 | black horse
-- out    5639 |              2 | HTML       |            2 | crown
-- out    5639 |              2 | HTML       |            3 | mitre
-- out    5639 |              3 | php        |            1 | black horse
-- out    5639 |              3 | php        |            2 | crown
-- out    5639 |              3 | php        |            3 | mitre
-- out    5639 |              4 | perl       |            1 | black horse
-- out    5639 |              4 | perl       |            2 | crown
-- out    5639 |              4 | perl       |            3 | mitre