Comma separated

• PSB

  SSChampion

  Points: 13195

  More actions

  July 21, 2016 at 8:30 am

  #311999

  Hi,

  I need to remove the extra comma before the NameComma field if for the same ID we have a NULL or '' (blank value) as well as ResourceName .

  CREATE TABLE #Temp

  (

  Name VARCHAR(100),

  ID INT

  )

  INSERT INTO #Temp (Name,ID)

  SELECT 'Resource1',1 UNION

  SELECT NULL,1 UNION

  SELECT 'Resource2',2 UNION

  SELECT 'Resource3',2

  SELECT * FROM #Temp

  SELECT DISTINCT STUFF(

  (SELECT ', ' + (ISNULL(t2.Name,''))

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  DROP TABLE #Temp

  Should be for ID = 1

  Resource1 instead of ,Resource1

  Thanks,

  PSB

• Sean Lange

  SSC Guru

  Points: 286589

  More actions

  July 21, 2016 at 8:47 am

  #1891348

  PSB (7/21/2016)

  ---

  Hi,

  I need to remove the extra comma before the NameComma field if for the same ID we have a NULL or '' (blank value) as well as ResourceName .

  CREATE TABLE #Temp

  (

  Name VARCHAR(100),

  ID INT

  )

  INSERT INTO #Temp (Name,ID)

  SELECT 'Resource1',1 UNION

  SELECT NULL,1 UNION

  SELECT 'Resource2',2 UNION

  SELECT 'Resource3',2

  SELECT * FROM #Temp

  SELECT DISTINCT STUFF(

  (SELECT ', ' + (ISNULL(t2.Name,''))

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  DROP TABLE #Temp

  Should be for ID = 1

  Resource1 instead of ,Resource1

  Thanks,

  PSB

  You can do this quite easily by simply excluding those rows from the subquery in STUFF.

  SELECT DISTINCT STUFF(

  (SELECT ', ' + (ISNULL(t2.Name,''))

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  and t2.Name > '' --this will eliminate empty strings and NULL

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 21, 2016 at 8:47 am

  #1891349

  Insert the comma in the ISNULL function.

  SELECT DISTINCT STUFF(

  (SELECT (ISNULL(', ' + t2.Name,''))

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  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

• Sean Lange

  SSC Guru

  Points: 286589

  More actions

  July 21, 2016 at 8:49 am

  #1891351

  Luis Cazares (7/21/2016)

  ---

  Insert the comma in the ISNULL function.

  SELECT DISTINCT STUFF(

  (SELECT (ISNULL(', ' + t2.Name,''))

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  This doesn't quite work if the value for name is an empty string.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 21, 2016 at 9:16 am

  #1891361

  You're right Sean, I missed the blank value part.

  I also prefer your solution as it should imply less work for the server.

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  July 21, 2016 at 9:30 am

  #1891368

  Thank You. It worked perfectly.

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  July 21, 2016 at 9:41 am

  #1891370

  Get rid of the ISNULL altogether. FOR XML automatically discards null expressions unless the XSINIL directive is specified.

  SELECT DISTINCT STUFF(

  (SELECT ', ' + t2.Name

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  Drew

  PS: The estimated plan shows that this is 0.1% faster than using a WHERE clause to exclude NULL values. Of course, I don't put much stock in the estimated plan on such a small table.

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Sean Lange

  SSC Guru

  Points: 286589

  More actions

  July 21, 2016 at 10:08 am

  #1891377

  drew.allen (7/21/2016)

  ---

  Get rid of the ISNULL altogether. FOR XML automatically discards null expressions unless the XSINIL directive is specified.

  SELECT DISTINCT STUFF(

  (SELECT ', ' + t2.Name

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  Drew

  PS: The estimated plan shows that this is 0.1% faster than using a WHERE clause to exclude NULL values. Of course, I don't put much stock in the estimated plan on such a small table.

  Drew this has the problem when there is an empty string for name.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  July 21, 2016 at 11:33 am

  #1891402

  Sean Lange (7/21/2016)

  ---

  drew.allen (7/21/2016)

  ---

  Get rid of the ISNULL altogether. FOR XML automatically discards null expressions unless the XSINIL directive is specified.

  SELECT DISTINCT STUFF(

  (SELECT ', ' + t2.Name

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  Drew

  PS: The estimated plan shows that this is 0.1% faster than using a WHERE clause to exclude NULL values. Of course, I don't put much stock in the estimated plan on such a small table.

  Drew this has the problem when there is an empty string for name.

  That's what I get for posting when I had trouble sleeping last night. Still, I wonder how it might perform with this change.

  SELECT DISTINCT STUFF(

  (SELECT ', ' + NULLIF(t2.Name, '')

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  Unfortunately, I don't have time to do a comparison.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Sean Lange

  SSC Guru

  Points: 286589

  More actions

  July 21, 2016 at 12:36 pm

  #1891428

  drew.allen (7/21/2016)

  ---

  Sean Lange (7/21/2016)

  ---

  drew.allen (7/21/2016)

  ---

  Get rid of the ISNULL altogether. FOR XML automatically discards null expressions unless the XSINIL directive is specified.

  SELECT DISTINCT STUFF(

  (SELECT ', ' + t2.Name

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  Drew

  PS: The estimated plan shows that this is 0.1% faster than using a WHERE clause to exclude NULL values. Of course, I don't put much stock in the estimated plan on such a small table.

  Drew this has the problem when there is an empty string for name.

  That's what I get for posting when I had trouble sleeping last night. Still, I wonder how it might perform with this change.

  SELECT DISTINCT STUFF(

  (SELECT ', ' + NULLIF(t2.Name, '')

  FROM #Temp t2

  where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

  FOR XML PATH (''))

  , 1, 1, '') AS Name

  FROM #Temp t1

  Unfortunately, I don't have time to do a comparison.

  Drew

  LOL. Using the NULLIF or a where predicate to remove NULL seems to be the same thing in the end. Both your query and mine produce identical execution plans. 😀

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/