Split Values Over Multiple Rows

Split values from many columns accordingly over multiple rows

You may try to transform the values in the L_VALUE, H_VALUE and UNIT columns as JSON (-10;25 into ["-10", "-25"]) and parse the values with additional OPENJSON() call. The result from the second OPENJSON() is a table with columns key, value and type and in case of an array, the key column contains the index of each item in the JSON array, so you need an appropriate JOINs:

Table and JSON:

DECLARE @JsonData NVARCHAR(MAX);
SET @JsonData = N'[
     {"id": 1, "lval": "-10;15", "hval": "-20;45", "unit": "kg;m"},
     {"id": 2, "lval": "-10;15;13", "hval": "-20;45;55", "unit": "kg;m;cm"},
     {"id": 3, "lval": "-10", "hval": "-20", "unit": "kg"}
]';
DECLARE @ExampleTable TABLE (
   EQ BIGINT, 
   L_VALUE NVARCHAR(100), 
   H_VALUE NVARCHAR(100), 
   UNIT NVARCHAR (30)
)

Statement:

INSERT INTO @ExampleTable
SELECT j.[EQ], a.[L_VALUE], a.[H_VALUE], a.[UNIT]
FROM OPENJSON(@JsonData) WITH (
   [EQ] BIGINT 'strict $.id',
   [L_VALUE] NVARCHAR(100) '$.lval',
   [H_VALUE] NVARCHAR(100) '$.hval',
   [UNIT] NVARCHAR(20) '$.unit'
) j
CROSS APPLY (
   SELECT l.[value], h.[value], u.[value]
   FROM OPENJSON(CONCAT('["', REPLACE(j.L_VALUE, ';', '","'), '"]')) l
   JOIN OPENJSON(CONCAT('["', REPLACE(j.H_VALUE, ';', '","'), '"]')) h ON l.[key] = h.[key]
   JOIN OPENJSON(CONCAT('["', REPLACE(j.UNIT, ';', '","'), '"]')) u ON l.[key] = u.[key]
) a (L_VALUE, H_VALUE, UNIT)

Result:

EQ L_VALUE H_VALUE UNIT
----------------------
1  -10     -20     kg
1  15      45      m
2  -10     -20     kg
2  15      45      m
2  13      55      cm
3  -10    -20      kg

Splitting a column into multiple rows

You can first split Code column on comma , then explode it to get the desired output.

df['Code']=df['Code'].str.split(',')
df=df.explode('Code')

OUTPUT:

  ID  A  B  C  D Code
0  1  a  z  s  m   AB
0  1  a  z  s  m   BC
0  1  a  z  s  m    A
1  2  b  x  d  j   AD
1  2  b  x  d  j   KL
2  3  c  y  w  j   AD
2  3  c  y  w  j   KL
3  4  a  x     h   AB
3  4  a  x     h   BC
4  5  b  y  s  m    A
5  6  b  z  s  h    A
6  7  c  x  s  h    B

If needed, you can replace empty string by NaN

Split single row value to multiple rows in Snowflake

I was able to resolve this by using LATERAL FLATTERN like a joining table and selecting the value from it.

SELECT DISTINCT A.VALUE AS COL_NAME 
FROM "DB"."SCHEMA"."TABLE", 
LATERAL SPLIT_TO_TABLE(COL_NAME,';')A

how to split single row to multiple rows in mysql

We can use a cross/inner join approach here with the help of SUBSTRING_INDEX():

SELECT
    t1.datetime1,
    t1.count,
    t1.num1,
    t2.num2
FROM
(
    SELECT datetime1, count, SUBSTRING_INDEX(num1, ',', 1) AS num1
    FROM yourTable
    UNION ALL
    SELECT datetime1, count, SUBSTRING_INDEX(num1, ',', -1)
    FROM yourTable
) t1
INNER JOIN
(
    SELECT datetime1, count, SUBSTRING_INDEX(num2, ',', 1) AS num2
    FROM yourTable
    UNION ALL
    SELECT datetime1, count, SUBSTRING_INDEX(num2, ',', -1)
    FROM yourTable
) t2
    ON t2.datetime1 = t1.datetime1
ORDER BY
    t1.datetime1,
    t1.num1,
    t2.num2;

Demo

Split value into multiple rows

You are on the right track with the table of numbers. You should start by adding more rows, so it matches (or exceeds) the maximum possible number of elements in a CSV list.

Then, you can use a join condition to generate the relevant number of rows per name only

select
    t.*,
    substring_index(substring_index(t.name, ';', n.n), ';', -1) name
from numbers n
inner join testLocation t   
    on n <= length(t.name) - length(replace(t.name, ';', '')) + 1

Demo on DB Fiddle (I expanded the numbers to 8):

id | State   | name                                    | name        
-: | :------ | :-------------------------------------- | :-----------
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Birmingham  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Huntsville  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Mobile      
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Montgomery  
 2 | Florida | Tampa;Jacksonville;Destin               | Tampa       
 2 | Florida | Tampa;Jacksonville;Destin               | Jacksonville
 2 | Florida | Tampa;Jacksonville;Destin               | Destin      

Note, that, as commented already by others, storing CSV lists in a database is bad practice and should almost always be avoided. Recommended related reading: Is storing a delimited list in a database column really that bad?

Split value from a total row to multiple other rows until the sum reaches the value of the total row in REDSHIFT

Window functions are your friend. When you have a query that compares rows you should first look to window functions on Redshift. This simpler, cleaner, and faster than any self joining pattern.

select 
  campaign,
  expected_inbound_date,
  expected_inbound_quantity,
  received_inbound_quantity,
  case when (inbound_total - inbound_sum) >= 0 then expected_inbound_quantity
       else case when (expected_inbound_quantity + inbound_total - inbound_sum) >= 0 then expected_inbound_quantity + inbound_total - inbound_sum
                else 0 end
    end as split

from (SELECT
  campaign,
  expected_inbound_date,
  expected_inbound_quantity,
  received_inbound_quantity,
  sum(expected_inbound_quantity) over (partition by campaign order by expected_inbound_date, expected_inbound_quantity) as inbound_sum,
  max(received_inbound_quantity) over (partition by campaign) as inbound_total

  FROM inbound i
) subq
ORDER BY 1,2,3,4; 

Updated fiddle here - https://dbfiddle.uk/?rdbms=postgres_13&fiddle=2381abdf5a90a997a4f05b809c892c40

When you port this to Redshift you may want to convert the CASE statements to DECODE() functions as these are more readable IMHO.

PS. Thank you for setting up the fiddle as this greatly speeds up providing an answer.

---

Related Topics