how to convert csv values to a table?

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  June 25, 2009 at 1:19 am

  #135739

  Hi,

  I couldnt find a way where i can get csv values in rows without using a while loop.

  how can i get the below ouput efficiently and fast?

  [Code]

  declare @s-2 varchar(500)

  set @s-2='1,2,3,4,5'

  [/Code]

  Expected Output:

  s

  --

  1

  2

  3

  4

  5

• arun.sas

  SSChampion

  Points: 11831

  More actions

  June 25, 2009 at 2:13 am

  #1015122

  descentflower (6/25/2009)

  ---

  csv values in rows without using a while loop

  Hi,

  try this

  --create table #temp (name1 varchar(5))

  declare @s-2 varchar(500)

  set @s-2='1,2,3,4,5'

  select @s-2 = 'select ''' + replace (@s,',',''' union select ''')+''''

  insert into #temp (name1)

  exec (@s)

  select * from #temp

  ARUN SAS

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  June 25, 2009 at 2:41 am

  #1015137

  Thats amazing thank you so much !! :w00t:

  Anything to take care using

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  June 25, 2009 at 3:10 am

  #1015149

  arun.sas (6/25/2009)

  ---

  descentflower (6/25/2009)

  ---

  csv values in rows without using a while loop

  Hi,

  try this

  --create table #temp (name1 varchar(5))

  declare @s-2 varchar(500)

  set @s-2='1,2,3,4,5'

  select @s-2 = 'select ''' + replace (@s,',',''' union select ''')+''''

  insert into #temp (name1)

  exec (@s)

  select * from #temp

  ARUN SAS

  And how to do opposite of this in an efficient manner?

  like Column to a CSV.

• Pandian S

  SSChasing Mays

  Points: 625

  More actions

  June 25, 2009 at 3:11 am

  #1015150

  [font="Courier New"]DECLARE @s-2 VARCHAR(500)

  DECLARE @X XML

  SELECT @s-2='1,2,3,4,5'

  SELECT @X = '<Data>' + REPLACE(@S,',','</Data><Data>') + '</Data>'

  SELECT N.value('.','varchar(max)') 'Result' from @X.nodes('Data') as T(N)[/font]

• Paul White

  SSC Guru

  Points: 150467

  More actions

  June 25, 2009 at 3:19 am

  #1015153

  declare @s-2 varchar(500), @x xml;

  select@s-2='1,2,3,4,5',

  @x = '' + REPLACE(@s, ',' , '') + '';

  selecta.value('.', 'int') as s

  from@x.nodes('/a') t(a)

  edit: I see 'Pandian S' beat me to it with the XML hack/solution :rolleyes:

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  June 25, 2009 at 3:34 am

  #1015161

  Pandian S (6/25/2009)

  ---

  [font="Courier New"]DECLARE @s-2 VARCHAR(500)

  DECLARE @X XML

  SELECT @s-2='1,2,3,4,5'

  SELECT @X = '<Data>' + REPLACE(@S,',','</Data><Data>') + '</Data>'

  SELECT N.value('.','varchar(max)') 'Result' from @X.nodes('Data') as T(N)[/font]

  Hi,This one takes a string as an input.

  If i have 5 rows in a table like

  tbItem

  item

  -----

  1

  2

  3

  4

  5

  then how can i get 1,2,3,4,5 as my output.

• Pandian S

  SSChasing Mays

  Points: 625

  More actions

  June 25, 2009 at 4:26 am

  #1015184

  [font="Verdana"]DECLARE @TABLE1TABLE (IDINT)

  INSERT @TABLE1 VALUES(1)

  INSERT @TABLE1 VALUES(2)

  INSERT @TABLE1 VALUES(3)

  INSERT @TABLE1 VALUES(4)

  INSERT @TABLE1 VALUES(5)

  DECLARE @IDsVARCHAR(MAX)

  SELECT @IDs = COALESCE(@IDs,'') + CAST(ID AS VARCHAR) + ',' FROM @TABLE1

  SELECT LEFT(@IDs,LEN(@IDs)-1) 'Result'

  (OR)

  ;WITH CTEs(Result) AS (SELECT CAST(ID AS VARCHAR) + ',' FROM @TABLE1 FOR XML PATH(''))

  SELECT LEFT(Result,LEN(Result)-1) 'Result' FROM CTEs[/font]

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  June 25, 2009 at 5:09 am

  #1015207

  Thanks a lot friends !! it helped me a lot !