Splitting comma seperated string into 2 rows in SQL Server

Question

How can I split a comma-separated row with 10 values into two rows, in which first row has the first 5 values and the next row has the last 5 values in SQL Server

Did you search for split string functions in Sql Server ? By the way which version of sql server you are using ? — Pரதீப், Jul 17 '17 at 04:03
please post some sample data and expected result as DDL and also show what you have attempted — TheGameiswar, Jul 17 '17 at 04:14
This isn't hard to do. You could use any string split function that includes a row number (or make your own custom one for this, but string split functions are not hard to find), then you'd use case aggregation with a mod function (e.g. `SELECT Val1 = MAX(CASE WHEN RN % 5 = 1 THEN Val END), Val2 = MAX(CASE WHEN RN % 5 = 2 THEN Val END)...`) and group by (RN - 1) / 5 — ZLK, Jul 17 '17 at 04:21
Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Jul 17 '17 at 06:36
Stop treating SQL tables like spreadsheets. Columns and Rows are *significantly* different concepts. Leave presentation concerns to a presentation layer like an application or report building tool. — Damien_The_Unbeliever, Jul 17 '17 at 08:15

score 0 · Answer 1 · answered Jul 17 '17 at 04:22

0

This is just an example how you can make it work

CREATE TABLE T
(ColSplit varchar(100)
)

INSERT INTO T VALUES
('a,b,c,d,e,f,g,h,i,j');

SELECT VALUE 
FROM STRING_SPLIT((SELECT ColSplit FROM T),',')

refer here for more information about STRING_SPLIT

answered Jul 17 '17 at 04:22

siddhartha jain

1,006
10
16

score 0 · Answer 2 · answered Jul 17 '17 at 08:07

You should really reconsider your database design.
Storing delimited lists in a database is a bad design, as I wrote in my comment.

However, if for some reason you can't change the database design, you can do something like this:

DECLARE @MyString varchar(100) = 'a,bc,def,ghij,klmno,pqrstu,vwxyz12,34567890,1qaz2wsx3,edc4rfv5tg,b6yhn7ujm'


;WITH CTE AS
(
    SELECT  @MyString As string,
            CHARINDEX(',', @MyString) As commaIndex,
            1 as commaNumber
    UNION ALL
    SELECT  string, 
            CHARINDEX(',', string, commaIndex + 1),
            commaNumber + 1
    FROM CTE
    WHERE CHARINDEX(',', string, commaIndex + 1) > 0
)


SELECT SUBSTRING(string, 1, commaIndex-1)
FROM CTE
WHERE commaNumber = 5

UNION ALL

SELECT SUBSTRING(string, commaIndex+1, LEN(string))
FROM CTE
WHERE commaNumber = 5

The result:

a,bc,def,ghij,klmno
pqrstu,vwxyz12,34567890,1qaz2wsx3,edc4rfv5tg,b6yhn7ujm

Splitting comma seperated string into 2 rows in SQL Server

2 Answers2