Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert Comma Separated column value to rows

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?

like image 587
mhn Avatar asked Dec 14 '12 06:12

mhn


People also ask

How do I get comma separated values into rows in Excel?

First select Cell B5, go to Data > Text to Columns. Then from the Text to Columns Wizard select Original Data Type: Delimited and click Next. Now choose the Delimiters type: Comma and click Next. After that, choose the Destination cell (here Cell C5) and press Finish.

How do you convert a column of numbers to a row with commas in Excel?

Please follow these instructions: 1. Type the formula =CONCATENATE(TRANSPOSE(A1:A7)&",") in a blank cell adjacent to the list's initial data, for example, cell C1. (The column A1:A7 will be converted to a comma-serrated list, and the separator "," will be used to separate the list.)


2 Answers

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

like image 174
SRIRAM Avatar answered Oct 03 '22 01:10

SRIRAM


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) 
like image 32
Sadacharan SS Avatar answered Oct 03 '22 01:10

Sadacharan SS