Need help on splitting CSV values

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  March 14, 2014 at 3:58 am

  #286716

  Hi there,

  following is the base table

  create table #sample (id int , value varchar(100))

  insert into #sample values (1,'aaa,bbb')

  insert into #sample values (2,'ccc,ddd')

  insert into #sample values (3,'aab')

  & i need the attached output

  

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  March 14, 2014 at 4:32 am

  #1697078

  Take a look at this article from Jeff Moden. http://www.sqlservercentral.com/articles/Tally+Table/72993/

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• Jim Mackenzie

  Hall of Fame

  Points: 3728

  More actions

  March 14, 2014 at 9:15 am

  #1697178

  Hi,

  HanShi is totally right.. After reading that article you'll end up with a query like the below:

  DECLARE @Sample TABLE

  (

  Id INT,

  Value VARCHAR(200)

  )

  INSERT INTO @Sample (Id,Value) VALUES (1,'aaa,bbb'),(2,'ccc,ddd'),(3,'aab')

  SELECT s.Id, item.Item

  FROM @Sample s

  CROSS APPLY [dbo].[DelimitedSplit8K](value,',') [item]

  Cheers,

  Jim.

  SQL SERVER Central Forum Etiquette[/url]