How to create a comma delimited string

• marty.seed

  SSCrazy

  Points: 2661

  More actions

  May 24, 2011 at 3:07 pm

  #236787

  Hello,

  Here is a sample data I am using.

  CREATE TABLE #temp

  (ae1 varchar,

  ae2 varchar,

  ae3 varchar,

  ae4 varchar)

  INSERT INTO #temp SELECT 1,2,null,null

  INSERT INTO #temp SELECT null,NULL,3,4

  INSERT INTO #temp SELECT null,null,NULL,4

  INSERT INTO #temp SELECT NULL,NULL,3,null

  INSERT INTO #temp SELECT 1,null,null,null

  I need to return this as

  ae1ae2ae3ae4Error Codes

  12NULLNULL1, 2

  NULLNULL343, 4

  NULLNULLNULL44

  NULLNULL3NULL3

  1NULLNULLNULL1

  I can't seem to figure out how to concatenate all the fields properly, especially when there are null values

  I tried Stuff() but wasn't having much luck although I figure my syntax was wrong.

  Any hints are appreciated!

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 24, 2011 at 4:02 pm

  #1329441

  There may be a more elegant solution that looks and/or performs better but this query gives the results you asked for:

  CREATE TABLE #temp

  (

  ae1 VARCHAR,

  ae2 VARCHAR,

  ae3 VARCHAR,

  ae4 VARCHAR

  )

  INSERT INTO #temp SELECT 1,2,null,null

  INSERT INTO #temp SELECT null,NULL,3,4

  INSERT INTO #temp SELECT null,null,NULL,4

  INSERT INTO #temp SELECT NULL,NULL,3,null

  INSERT INTO #temp SELECT 1,null,null,null

  WITH cte(ae1, ae2, ae3, ae4, concat_string)

  AS (

  SELECT ae1,

  ae2,

  ae3,

  ae4,

  ISNULL(ae1 + ',', '') + ISNULL(ae2 + ',', '') + ISNULL(ae3 + ',', '') + ISNULL(ae4 + ',', '') AS concat_string

  FROM #temp

  )

  SELECT ae1,

  ae2,

  ae3,

  ae4,

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

  FROM cte ;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• WayneS

  SSC Guru

  Points: 95460

  More actions

  May 24, 2011 at 7:36 pm

  #1329463

  whoops... opc.three has it covered.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 24, 2011 at 9:50 pm

  #1329472

  One tweak I would make to mine is to trade in the two separate function calls to LEFT and LEN for one call to STUFF:

  CREATE TABLE #temp

  (

  ae1 VARCHAR,

  ae2 VARCHAR,

  ae3 VARCHAR,

  ae4 VARCHAR

  ) ;

  INSERT INTO #temp SELECT 1,2,NULL,NULL ;

  INSERT INTO #temp SELECT NULL,NULL,3,4 ;

  INSERT INTO #temp SELECT NULL,NULL,NULL,4 ;

  INSERT INTO #temp SELECT NULL,NULL,3,NULL ;

  INSERT INTO #temp SELECT 1,NULL,NULL,NULL ;

  INSERT INTO #temp SELECT NULL,NULL,NULL,NULL ;

  WITH cte ( ae1, ae2, ae3, ae4, concat_string )

  AS (

  SELECT ae1,

  ae2,

  ae3,

  ae4,

  ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, '') AS concat_string

  FROM #temp

  )

  SELECT ae1,

  ae2,

  ae3,

  ae4,

  STUFF(concat_string, 1, 1, '') AS concat_string

  FROM cte ;

  Edit

  PS and at that point who needs the cte?

  SELECT ae1,

  ae2,

  ae3,

  ae4,

  STUFF(ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, ''), 1, 1, '') AS concat_string

  FROM #temp ;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• marty.seed

  SSCrazy

  Points: 2661

  More actions

  May 25, 2011 at 7:28 am

  #1329654

  Thanks so much, I knew I was close but the last post really cleared up how the STUFF function works!