How to split multiple values in single cell into multiple columns in sql server???

Question

My input is

Table1

 id   items

1   92,93     

2  12,13

c1 c2 

92 93 

12 13 

Can anyone help me how to do it??

Answer 1

Hi Sanjitha,

try this,

I have referenced this link - http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/95cb28f6-5439-41a1-9172-a48107ef61e1

DECLARE @tab TABLE (items VARCHAR(50))
INSERT INTO @tab SELECT '92,93' 
INSERT INTO @tab SELECT '12,13'
SELECT q.s1 Column1,r.s2 Column2
FROM @tab
CROSS APPLY (SELECT CHARINDEX(',',items) AS px)p
CROSS APPLY (SELECT SUBSTRING(items,1,p.px-1) AS s1)q
CROSS APPLY (SELECT SUBSTRING(items,p.px+1,LEN(items)) AS s2)r

Here are few more threads:

http://social.msdn.microsoft.com/Forums/en-GB/transactsql/thread/502b065b-d711-4028-a2e6-7807d16973be

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/05772dd0-27b0-4e50-a26b-28bea265a344

http://social.msdn.microsoft.com/Forums/da-DK/transactsql/thread/88933ed8-3d3e-4c2c-af91-146a3284e59b

---

Thanks & Regards, sathya