CONVERT DATA COLUMN TO ROW WTIH COMMA

• anuj12paritosh

  SSC Veteran

  Points: 295

  More actions

  August 20, 2012 at 8:51 am

  #260311

  HI ALL,

  HOW TO COVERT COLUMN DATA IN TO ROW WITH COMMA SEPARATOR

  DATA LIKE THAT :-

  TABLE NAME : - TEST

  COLUMN NAME : - COL1

  ORIGINAL DATA ARE :-

  COL1

  1

  2

  3

  4

  5

  6

  7

  1

  4

  5

  REQUIRED DATA ARE :-

  1,2,3,4,5,6,7,1,4,5

  HOW TO DO THIS ....

  THANKS IN ADVANCE....

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 20, 2012 at 9:04 am

  #1527665

  Something like this?

  DECLARE @test-2 table(

  col1int)

  INSERT INTO @test-2

  SELECT 1 UNION ALL SELECT

  2 UNION ALL SELECT

  3 UNION ALL SELECT

  4 UNION ALL SELECT

  5 UNION ALL SELECT

  6 UNION ALL SELECT

  7 UNION ALL SELECT

  1 UNION ALL SELECT

  4 UNION ALL SELECT

  5;

  WITH CTE AS(

  SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','

  FROM @test-2 b

  FOR XML PATH('')

  ) AS VARCHAR(MAX)) concat_string

  )

  SELECT LEFT( concat_string, LEN(concat_string) - 1)

  FROM CTE

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• wlee88

  Newbie

  Points: 3

  More actions

  August 21, 2012 at 12:36 pm

  #1528323

  This is what I do.

  ex:

  col1

  1

  2

  3

  4

  5

  6

  declare @text varchar(500)

  select @text = isnull(@text + ',','') + col1 from table1

  select @text

  Hope this help.

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 21, 2012 at 10:15 pm

  #1528438

  Using Luis' setup:

  DECLARE @test-2 table(

  col1int)

  INSERT INTO @test-2

  SELECT 1 UNION ALL SELECT

  2 UNION ALL SELECT

  3 UNION ALL SELECT

  4 UNION ALL SELECT

  5 UNION ALL SELECT

  6 UNION ALL SELECT

  7 UNION ALL SELECT

  1 UNION ALL SELECT

  4 UNION ALL SELECT

  5;

  DECLARE @s-2 VARCHAR(8000);

  SELECT

  @s-2 = COALESCE(@S, '') + CAST(col1 AS VARCHAR(10)) + ','

  FROM @test-2;

  SELECT LEFT(@S, LEN(@S)-1); --LEFT is to remove comma at the end

  --Vadim R.

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  August 21, 2012 at 11:50 pm

  #1528452

  Slight modification in CTE:

  WITH CTE AS(

  SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','

  FROM @test-2 b

  FOR XML PATH('')

  ) AS VARCHAR(MAX)) concat_string

  )

  SELECT STUFF(concat_string,1,1,'')

  FROM CTE

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter

  

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 22, 2012 at 12:09 am

  #1528457

  Lokesh Vij (8/21/2012)

  ---

  Slight modification in CTE:

  WITH CTE AS(

  SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','

  FROM @test-2 b

  FOR XML PATH('')

  ) AS VARCHAR(MAX)) concat_string

  )

  SELECT STUFF(concat_string,1,1,'')

  FROM CTE

  Result:

  ,2,3,4,5,6,7,1,4,5,

  --Vadim R.

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  August 22, 2012 at 2:21 am

  #1528496

  rVadim (8/22/2012)

  ---

  Lokesh Vij (8/21/2012)

  ---

  Slight modification in CTE:

  WITH CTE AS(

  SELECT CAST( (SELECT CAST( col1 AS varchar(10))+ ','

  FROM @test-2 b

  FOR XML PATH('')

  ) AS VARCHAR(MAX)) concat_string

  )

  SELECT STUFF(concat_string,1,1,'')

  FROM CTE

  Result:

  ,2,3,4,5,6,7,1,4,5,

  Here is the corrected version:

  WITH CTE AS(

  SELECT CAST( (SELECT ',' + CAST( col1 AS varchar(10)) FROM @test-2 b

  FOR XML PATH('')

  ) AS VARCHAR(MAX)) concat_string

  )

  SELECT STUFF(concat_string,1,1,'')

  FROM CTE

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter