Comma Separated values to multiple rows | string_split() | SQL Interview Q&A | CROSS APPLY

*****************************************************************************
-- 22 - STRING_SPLIT() - Comma Separated values to multiple rows
--*****************************************************************************
DROP TABLE Employees

CREATE TABLE Employees (Empid INT, ID Varchar(150))

INSERT Employees VALUES (1, '2,3,4,5,6')
INSERT Employees VALUES (2, '7,8,9,10,11')

select * From Employees
select * From Employees CROSS APPLY string_split(id,',')
SELECT Empid, Value FROM Employees CROSS APPLY string_split(ID,',')

Video 21: Comma Separated values in SQL | XML PATH | SQL to XML Format | Row Tag| Root Tag | SQL Interview Q&A

Facebook Pages:

This channel (SQL With RaviMartha) would provide new videos on SQL, ETL and Data warehouse concepts. I would create a separate play list for SQL Tutorials for beginners, advanced concepts, Interview questions and data warehousing concepts

Youtube Channel:

[dinesh-krns]

Can you give the query which are you following without using string_split(), because im using SQL server 2008r2.

[rt_]

Very helpful, thank you very much Ravi 👍

[Shankar-ziyk]

May i know why are you specifying value in select statement

[anandruparelia]

But what if we have multiple columns with comma separated values?
If we have one, we can handle this using string_split, what about more than columns?

[jeetsutaria]

What if we want to do the opposite of this int rows into single column

[EfficientWay]

Is there any alternate query expect to STRING_SPLIT method.
It is not working for me.

[vivekparashar]

How the cross apply worked here as we have not provided any where condition. Also, I believe if we don’t apply the where condition then it connect every row of first table with another something like cross join. Can you pls help to understand. Thanks

[lizaaguilar]

hi, how can I use this in PostgreSQL? whats the the right query?thanks

[Sharmine_]

Thanks for the video. However, CROSS APPLY is syntax error for me. Do you have any idea on how to fix it? I'm using MySQL Workbench 8.0 version

[dillip]

can you please tell the query for MYSQL?

[ipadhardex]

Ravimartha, got messages this Argument data type int is invalid for argument 1 of string_split function.

[sravanthisettipalli]

Please write same comma seperated data in 2014 version

[saurabhkondkar]

#error
procedure expects parameter 'seperator' of type 'nchar(1)/nvarchar(1)'

[hanumegowdabh]

sql server 2005 how to split, or | separation text ?

[radhapatel]

Ohhhh my God 3 days ago it was asked in my interview and I was failed