SQL split values to multiple rows
If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
numbers inner join tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
id, n
Please see fiddle here.
If you cannot create a table, then a solution can be this:
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
(select 1 n union all
select 2 union all select 3 union all
select 4 union all select 5) numbers INNER JOIN tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
id, n
an example fiddle is here.
Splitting delimited values in a SQL column into multiple rows
If you are on SQL Server 2016+
You can use the new STRING_SPLIT function, which I've blogged about here, and Brent Ozar has blogged about here.
SELECT s.[message-id], f.value
FROM dbo.SourceData AS s
CROSS APPLY STRING_SPLIT(s.[recipient-address], ';') as f;
If you are still on a version prior to SQL Server 2016
Create a split function. This is just one of many examples out there:
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
) AS y);
GO
I've discussed a few others here, here, and a better approach than splitting in the first place here.
Now you can extrapolate simply by:
SELECT s.[message-id], f.Item
FROM dbo.SourceData AS s
CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;
Also I suggest not putting dashes in column names. It means you always have to put them in [square brackets].
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 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
SQL: Split rows out of values from multiple columns
You can use union all:
select concat('State, ', StateName) as Name, StateValue as Value
from table_name
union all
select concat('City, ', CityName) as Name, CityValue as Value
from table_name;
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
ORACLE: Splitting a string into multiple rows
From Oracle 12, you can use:
SELECT branch_code, year, month, day, holiday
FROM branches
CROSS JOIN LATERAL (
SELECT LEVEL AS day,
SUBSTR(holiday_list, LEVEL, 1) AS holiday
FROM DUAL
CONNECT BY LEVEL <= LENGTH(holiday_list)
)
Which, for the sample data:
CREATE TABLE branches (BRANCH_CODE, YEAR, MONTH, HOLIDAY_LIST) AS
SELECT '001', 2021, 1, 'HHHWWWHHHHWWWWWHHWWWWWHHWWWWWHH' FROM DUAL UNION ALL
SELECT '001', 2021, 2, 'WWWWWHHWWWWWHHWWWWWHHWHWWWHH' FROM DUAL
Outputs:
BRANCH_CODE YEAR MONTH DAY HOLIDAY 001 2021 1 1 H 001 2021 1 2 H 001 2021 1 3 H 001 2021 1 4 W ... ... ... ... ... 001 2021 1 29 W 001 2021 1 30 H 001 2021 1 31 H 001 2021 2 1 W 001 2021 2 2 W 001 2021 2 3 W ... ... ... ... ... 001 2021 2 26 W 001 2021 2 27 H 001 2021 2 28 H
Related Topics
Error When Trying to Install App With MySQL2 Gem
Difference Between Lateral Join and a Subquery in Postgresql
MySQL Query Finding Values in a Comma Separated String
Equivalent of Limit and Offset For SQL Server
Best Way to Test If a Row Exists in a MySQL Table
What This Query Does to Create Comma Delimited List SQL Server
What Is the Benefit of Using "Set Xact_Abort On" in a Stored Procedure
Select Max Value of Each Group
Correct MySQL Configuration For Ruby on Rails Database.Yml File
How Does Database Indexing Work
Foreign Key Constraint May Cause Cycles or Multiple Cascade Paths
How to Cast the Datetime to Time
MySQL Insert Query Doesn't Work With Where Clause
Group by Clause in MySQL and Postgresql, Why the Error in Postgresql