Make multiple rows from cell value

Question

I have a column named Item(varchar(max)) in my SQL server it contains cell data as follow:

'1|2|3|4|5'

I want output as :

Is it possible to do so using SQL Server Management Studio.

Yes. Here is a good reference for a bunch of ways to split strings: https://sqlperformance.com/2012/07/t-sql-queries/split-strings — ZLK, Jan 04 '17 at 21:11
The best solution would be to stop storing delimited values. It violates 1NF and is a serious pain to work with. — Sean Lange, Jan 04 '17 at 21:13
For SQL Server **2016**, use `SELECT value FROM STRING_SPLIT('1|2|3|4|5','|')` — ahmed abdelqader, Jan 04 '17 at 21:24

Tyler Roper · Accepted Answer · 2017-01-04T21:32:03.753

0

Though I highly advise separating yourself from delimited values, if you have to use them, you can use the following method:

Create the Table Function:

CREATE FUNCTION dbo.splitStringByPipe
(@string nvarchar(500))
RETURNS TABLE 
AS
RETURN 
(
 SELECT  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT  
         CAST ('<M>' + REPLACE(@string, '|', '</M><M>') + '</M>' AS XML) AS String) AS A CROSS APPLY String.nodes ('/M') AS Split(a)     
)
GO

Execute the function:

SELECT * FROM dbo.splitStringByPipe('1|a|5|z|r')

Returns:

  | String
1 | 1
2 | a
3 | 5
4 | z
5 | r

edited Jan 04 '17 at 21:32

answered Jan 04 '17 at 21:15

Tyler Roper

21,445
6
33
56

Sir, i want count of '1' after receiving output as desired..Please Help – Utkarsh Agrawal Jan 04 '17 at 21:31
Please provide an example of the table you'll be selecting from, and your final desired output. Without knowing anything about your structure, I cannot elaborate on my answer any further. – Tyler Roper Jan 04 '17 at 21:37

score 0 · Answer 2 · answered Jan 04 '17 at 21:37

FOR XML (SQL Server)

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query.

More Detail for XML(SQL Server)

;WITH CTE1
AS
(
SELECT CAST(('<r><c>' + REPLACE(TestColumn, '|', '</c></r><r><c>') + '</c></r>' ) AS XML) XmlColumn
FROM tbl_Employee
),
TBL2
AS
(
SELECT  CAST(Col.query('./text()') AS VARCHAR(10))Result
FROM 
CTE1 CROSS APPLY 
XmlColumn.nodes('/r/c') As Tab(Col)
)
SELECT * FROM TBL2

Output :

 Result 
-  1 -
-  2 -
-  3 -
-  4 - 
-  5 -

Make multiple rows from cell value

2 Answers2