CODEWITHSUNDEEP
sqlssrs-2008ssrs-2012ssrs-2008-r2
Piyush Jain
Piyush Jain

Reputation: 5

SQL split row contents to multiple rows

Hello I am new to sql and I am not sure if it is possible like below

I have a row like this:

abc,xyz; bcd,ddd; qqq,eee

I want to write a query which will split the row to multiple rows like this:

abc,xyz
bcd,ddd
qqq,eee

Rows may contain 1 or 2 or 3 names or null, it is not fixed.

How can I do this?

Upvotes: 0

Views: 776

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Try some XML node methods which would turn your comma separated value into multiple rows 

SELECT 
      LTRIM(a.value('.', 'NVARCHAR(MAX)')) [Data] FROM
(
    SELECT CAST('<M>'+REPLACE(<column>, ';', '</M><M>')+'</M>' AS XML) AS DATA from <table_name>
) A CROSS APPLY DATA.nodes('/M') as split(a)

Result :

abc,xyz
bcd,ddd
qqq,eee

Upvotes: 1

Hogan
Hogan

Reputation: 70526

SELECT STRING_SPLIT ( fieldname, '; ')  
FROM tablename

or maybe you want a CROSS JOIN

SELECT z.field_a, z.field_b, x.VALUE
FROM tablename as z
CROSS JOIN (SELECT VALUE FROM STRING_SPLIT ( z.fieldname, '; ')) AS x

or maybe you want a CROSS APPLY

read this: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

Upvotes: 0

Related Questions