How to split a comma-separated value to columns
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0
SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END
WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)
INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]
SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END
RETURN
END
Turning a Comma Separated string into individual rows
You can use the wonderful recursive functions from SQL Server:
Sample table:
CREATE TABLE Testdata
(
SomeID INT,
OtherID INT,
String VARCHAR(MAX)
);
INSERT Testdata SELECT 1, 9, '18,20,22';
INSERT Testdata SELECT 2, 8, '17,19';
INSERT Testdata SELECT 3, 7, '13,19,20';
INSERT Testdata SELECT 4, 6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';
The query
WITH tmp(SomeID, OtherID, DataItem, String) AS
(
SELECT
SomeID,
OtherID,
LEFT(String, CHARINDEX(',', String + ',') - 1),
STUFF(String, 1, CHARINDEX(',', String + ','), '')
FROM Testdata
UNION all
SELECT
SomeID,
OtherID,
LEFT(String, CHARINDEX(',', String + ',') - 1),
STUFF(String, 1, CHARINDEX(',', String + ','), '')
FROM tmp
WHERE
String > ''
)
SELECT
SomeID,
OtherID,
DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option
Output
SomeID | OtherID | DataItem
--------+---------+----------
1 | 9 | 18
1 | 9 | 20
1 | 9 | 22
2 | 8 | 17
2 | 8 | 19
3 | 7 | 13
3 | 7 | 19
3 | 7 | 20
4 | 6 |
9 | 11 | 1
9 | 11 | 2
9 | 11 | 3
9 | 11 | 4
Split comma separated string table row into separate rows using TSQL
Alternatively, you could use XML like so:
DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));
INSERT INTO @yourTable
VALUES (1,'a,b,c,d'),
(2,'e,f,g');
WITH CTE
AS
(
SELECT ID,
[xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML)
FROM @yourTable
)
SELECT ID,
[SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)
How can I get comma delimited values into new row in BigQuery?
Try this one:
with mytable as (
select "X" as model, "1,34,60,1000" as alarm union all
select "Y", "2,4,5,900,1000" union all
select "Z", "1"
)
select model, newalarm
from mytable, unnest(split(alarm)) as newalarm
How to split more than one comma separated column as a separate row in SQL using CROSS APPLY
Since - according to your description - you used CROSS APPLY
and your query was successfully executed, this means you are using a SQL Server DB, not MY SQL. You can do two CROSS APPLY
to get your expected result. This will produce exactly the outcome you have shown in your question:
SELECT name, phone, value courses FROM
(SELECT name, value phone, courses
FROM tblName CROSS APPLY STRING_SPLIT(phones, ',')) x
CROSS APPLY STRING_SPLIT(courses, ',')
ORDER BY name, courses, phone;
You can verify this here: db<>fiddle
But this is very risky and you really should avoid such comma-separated contents in one column. I highly recommend to create separate columns for the different values in future.
How to split the row by a comma and unpivot the table in PostgreSQL?
you can use string_to_array()
and unnest()
SELECT t.birthday_id ,
t.child_birthday_id,
p.place
FROM sh.test t
CROSS JOIN LATERAL unnest(string_to_array(place, ',')) as p(place);
If you are using Postgres 14 you can also use string_to_table(t.place, ',')
Related Topics
MySQL, Update Multiple Tables With One Query
How to Access the "Previous Row" Value in a Select Statement
Datetime2 VS Datetime in SQL Server
Tsql Pivot Without Aggregate Function
How to Generate a Range of Numbers Between Two Numbers
Postgresql Distinct on With Different Order By
Difference Between Join and Inner Join
Does Postgresql Support "Accent Insensitive" Collations
MySQL - How to Unpivot Columns to Rows
Count(*) VS Count(1) - SQL Server
Select Rows Which Are Not Present in Other Table