CODEWITHSUNDEEP
sqlsql-serversplitsql-server-2012
SQLBlog
SQLBlog

Reputation: 21

need to split a column value

I have a below table

id  name    total
1   a         2
2   b         3
3   c,d,e,f   15

Expected Output:-

id  name    total
1   a        2
2   b        3
3   c        15
4   d        15
5   e        15
5   f        15

I tried split function and also XML, but didn't work.

Upvotes: 0

Views: 62

Answers (2)

Alex
Alex

Reputation: 1692

Which version of SQL are you using?

The split function is for splitting a string of text, but what you are requesting is a change to the format of the table itself.

Your table has a tuple of id=3, name=c,d,e,f, total=15.

If you want id=3, name=c and so on, you have to change the data.

From the way your question is phrased, it implies that you want the data to be presented in a different way, but the id is the defining column which differentiates between rows in the database.

You could automatically generate a new table, in which case the split statement would be useful to get each element out of your comma separated record.

Once you have that list of items, assuming your id field is an identity field (auto incrementing), you could run an insert statement for each element.

You might be able to get the sort of output you're looking for using an inner select that splits the comma separated list of values, but you would need some procedural SQL (or T-SQL... you do not specify your SQL server) to iterate over the values and insert them into a new table. If you do go down this route, the id values will have to be thrown away, and you would treat the list as just a raw data set.

EDIT: The example posted by Have No Display Name is about as close as you're going to get with the data in the form it is. The IDs for the names 'c','d','e' and 'f' will all be 3, but your format will be very close.

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8517

As you dont specify the DB name, Assuming SQL SERVER. You can try this one.

Working Example

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

Refer this article

Upvotes: 1

Related Questions