Split Comma Separated Values to Column

Question

Post reply

Split Comma Separated Values to Column

yogi123

SSCrazy

Points: 2932
More actions
July 11, 2019 at 2:02 pm

#3661965
Hi Guys,
I have one task and i need to build the logic to split the columns by comma,
see below for table definition
```
CREATE TABLE #X1
(SCHID VARCHAR(250))

INSERT INTO #X1 VALUES ('101,102,103,104,105,106,107')
```
The desired output should be,
```
IDAdditionalIds
101102|103|104|105|106|107
```
Can you please help me to build this? I appropriate for your help, and Thanks for your help.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andrey SSC Eights! Points: 840 More actions · Answer 1

all you need is String Splitter

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

So the number of columns returned = (number 0f commas + 1), is that correct?

What happens if the number of commas varies by input data row?

What should the columns be called?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yogi123 SSCrazy Points: 2932 More actions · Answer 3

Thanks for your reply,

The First Column need to have only one Value before comma, and rest it need to go to other column.

Thom A SSC Guru Points: 99130 More actions · Answer 4

Seems like the easiest would be to use CHARINDEX and STUFF:

SELECT I.ID,
       I.AdditionalIDs
FROM (VALUES ('101,102,103,104,105,106,107')) X1(SCHID)
     CROSS APPLY (VALUES(LEFT(X1.SCHID,NULLIF(CHARINDEX(',',X1.SCHID),0)-1),STUFF(X1.SCHID,1,NULLIF(CHARINDEX(',',X1.SCHID),0),'')))I(ID,AdditionalIDs);

You could also write the above as follows, which may be easier to read:

SELECT I.ID,
       I.AdditionalIDs
FROM (VALUES ('101,102,103,104,105,106,107')) X1(SCHID)
     CROSS APPLY (VALUES(NULLIF(CHARINDEX(',',X1.SCHID),0))) CI(I)
     CROSS APPLY (VALUES(LEFT(X1.SCHID,CI.I-1),STUFF(X1.SCHID,1,CI.I,'')))I(ID,AdditionalIDs);

This reply was modified 5 years, 4 months ago by Thom A. Reason: Added extra version

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Andrey SSC Eights! Points: 840 More actions · Answer 5

or such one:


with data as 
(select '101,102,103,104,105,106,107' [SCHID]) 
select 
    left(SCHID,charindex(',',SCHID)-1) [Id]
    ,stuff(SCHID,1,charindex(',',SCHID),'') [AdditionalIds]
from data

Andrey SSC Eights! Points: 840 More actions · Answer 6

Thom A wrote:

You could also write the above as follows, which may be easier to read

with all due respect, such constructions are "easier to read" if you deal with or create them on a regular basis 😉

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

Andrey wrote:

Thom A wrote:
You could also write the above as follows, which may be easier to read
with all due respect, such constructions are "easier to read" if you deal with or create them on a regular basis 😉

Which is why the qualifying words may be were used.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yogi123 SSCrazy Points: 2932 More actions · Answer 8

Thanks Guys,

It helps me to develop in my logic.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

SELECT LEFT(SCHID, x.Pos - 1) [Id],
       REPLACE(SUBSTRING(SCHID, x.Pos + 1, 8000),',','|') [AdditionalIds]
  FROM #X1
 CROSS APPLY(VALUES (CHARINDEX(',', SCHID))) x(Pos)