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?

Brigandine

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.... – Rockey

tell me more about your problem! what is your table? or it's schema – Roentgenology

First of all, you're using a bad data model. Comma-delimited strings should prettymuch never be in databases. Period. – Souvenir

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. – Asbestosis

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 #3653962 – Equalize

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 ;) – Asbestosis

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. – Biannulate

Answer 1

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

Naught

Nice answer, but for special characters or multilingual need to change Split.a.value('.', 'VARCHAR(100)') to Split.a.value('.', 'NVARCHAR(100)') – Flatling

Answer 2

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)

Melodiemelodion

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... – Humanoid

Whilst this code snippet is welcome, and may provide some help, it would be greatly improved if it included an explanation 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. – Wolfenbarger

Answer 3

New way of doing this:

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

Wayzgoose

Answer 4

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

Dael

Answer 5

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)

Seductress