Comma Delimited List from Query

• kknobloch

  SSC Veteran

  Points: 267

  More actions

  November 17, 2010 at 9:57 am

  #241255

  I have a Stored Procedure that returns a comma delimited list (ex: one, two, seven, nine) - when I pull this into SSRS 2008 rather than it displaying as: one, two, seven, nine in a textbox I need it to display as:

  one

  two

  seven

  nine

  Each item in the list on its own line.

  Any ideas?

  Thanks in advance!!!

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  November 17, 2010 at 11:25 am

  #1252051

  EDIT: Whoops, cross posted by accident, had two windows going at once and punched this into the wrong one. Correct response below:

  You want a splitter function. Check this out:

  http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589

  That'll split it for you.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• WayneS

  SSC Guru

  Points: 95460

  More actions

  November 17, 2010 at 11:29 am

  #1252056

  kknobloch (11/17/2010)

  ---

  I have a Stored Procedure that returns a comma delimited list (ex: one, two, seven, nine) - when I pull this into SSRS 2008 rather than it displaying as: one, two, seven, nine in a textbox I need it to display as:

  one

  two

  seven

  nine

  Each item in the list on its own line.

  Any ideas?

  Thanks in advance!!!

  1. Re-write the stored procedure to not create the output in a column-delimited list.

  2. Write a new stored procedure to not create the output in a column-delimited list.

  3. Utilize the DelimitedSplit8K function to break the column-delimited list into separate items. Click here for the latest Delimited Split Function.

  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