Split Row

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  July 30, 2015 at 10:44 am

  #306206

  I have a table where there are two managers in the Name section together . When I try to filter it with = it results in an error.

  I want to split it in two rows and pass the respective name to retrieve data.Tried XML and usde the split function but looks like its not splitting...Any Idea...

  Here it is .

  CREATE TABLE #tbl_data (

  Manager Varchar(25),

  Device Varchar(20))

  INSERT INTO #tbl_data (

  Manager,

  Device

  )

  VALUES

  ( 'Andy','ABC123'),

  ( 'John ,Sue' ,'XYZ456')

  --( NULL, 'BC');

  --Drop table #tbl_data

  Select * from #tbl_data

  XML ...

  Select Device , Manager from (Select Device , CAST ('<M>' + REPLACE(Manager, ';', '</M><M>') + '</M>' AS XML) AS Manager FROM #tbl_data) A

  CROSS APPLY Manager.nodes('/M') AS Split(a)

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  July 30, 2015 at 11:06 am

  #1816237

  Use DelimitedSplit8K[/url]

  something like this:

  SELECT test.Manager, test.Device, split.ItemNumber, Item = RTRIM(split.Item)

  FROM #tbl_data test

  CROSS APPLY dbo.DelimitedSplit8k(test.Manager,',') split;