Convert Comma Separated column value to rows

Question

I have a table Sample with data stored like below

Id   | String
--------------
1     abc,def,ghi
2     jkl,mno,pqr

I need the output like..

Id   | processedrows
--------------
1     abc
1     def
1     ghi
2     jkl
2     mno
2     pqr

How can I do the same with a select query in SQL Server?

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using.... — marc_s, Dec 14 '12 at 06:23
tell me more about your problem! what is your table? or it's schema — frogatto, Dec 14 '12 at 06:24
First of all, you're using a bad data model. Comma-delimited strings should prettymuch never be in databases. Period. — , Dec 14 '12 at 06:25
While people are willing to help, please search the archives first. Splitting a CSV string is a very common question. Though I agree with Jack Maney, if at all possible - you should change your data model. Storing CSV strings is a recipe for trouble. — Leigh, Dec 14 '12 at 06:46
Never is long time. I completely agree that storing comma-separated lists is a recipe for trouble, but denormalization has its place. That said, storing lists in a single column and then access the elements of the list individually with SQL is bound to be awkward. See http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad/3653574#3653574 — Bill Karwin, Dec 14 '12 at 06:47
Agreed, few things are 100% absolute. Maybe another way to put it is "do not use that structure without a good reason and understanding of the drawbacks and limitations." (If you have to ask what those are - you probably do not have a compelling reason to use it ;) — Leigh, Dec 17 '12 at 22:08
Why are so many comments telling this guy that his data model is "wrong"? In my experience, you don't always get to dictate the data model, or maybe this model works just fine for him. Either he's dealing with someone else's mistake or he's got the data model he wants. — FistOfFury, Jul 02 '14 at 19:55

score 54 · Accepted Answer · edited Dec 14 '12 at 06:33

54

try this

 SELECT A.[id],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [id],  
         CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

refer here

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html

edited Dec 14 '12 at 06:33

senK

2,682
1
26
38

answered Dec 14 '12 at 06:30

SRIRAM

1,888
2
17
17

1

Thanks! It helped me! – Gidil Sep 03 '15 at 14:02
Awesome. Saved lot of time! – Shahdat Jul 28 '16 at 14:19
2

Nice answer, but for special characters or multilingual need to change Split.a.value('.', 'VARCHAR(100)') to Split.a.value('.', 'NVARCHAR(100)') – GMD Mar 31 '17 at 05:26

score 1 · Answer 2 · edited May 01 '17 at 11:38

1

SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

edited May 01 '17 at 11:38

pacholik

8,138
9
46
52

answered May 01 '17 at 11:02

Sadacharan SS

11
1

I'm not sure if this answers the question, but it doesn't seem to; the question doesn't have a field called `EmployeeID`, and turning the string values into XML seems like massive overkill to me... – DaveyDaveDave May 01 '17 at 11:40
Whilst this code snippet is welcome, and may provide some help, it would be [greatly improved if it included an explanation](//meta.stackexchange.com/q/114762) of *how* it addresses the question. Without that, your answer has much less educational value - remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight May 01 '17 at 12:17

score 1 · Answer 3 · answered Oct 26 '21 at 11:14

1

New way of doing this:

    SELECT
         a.Id,
         b.value
    FROM
         Sample a 
         cross apply string_split(a.string,',') b

answered Oct 26 '21 at 11:14

Sandeep Sirohi

11
2

score 0 · Answer 4 · edited Apr 17 '15 at 13:42

0

Let's try the below script:-

declare @str varchar(max)

SELECT @str = isnull(@str +',', '') + a.Value
FROM (SELECT Value Entityvalue from Table) a

select @str

edited Apr 17 '15 at 13:42

Community

1
1

answered Jul 25 '14 at 13:26

RickyRam

171
1
1
10

score 0 · Answer 5 · edited Apr 17 '15 at 13:40

0

Try with this. You will get your output.

SELECT id,
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Values' 
FROM  
(
     SELECT algorithms,
     CAST ('<M>' + REPLACE(string, ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM   <TableName> 
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

edited Apr 17 '15 at 13:40

shA.t

15,880
5
49
104

answered Apr 17 '15 at 13:09

Phani

83
1
7

Convert Comma Separated column value to rows

5 Answers5

Linked

Related