• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

return string from selected items in column

I would like to select all from a column and return it as string with '/' between the colors. How do I do that?

For example: MyTable(Id, Colors)
The column Colors has 'Brown','Blue','Green'
I would like to return: 'Brown/Blue/Green'  
If there is only one color that is returned I would not like to show '/' (for example: 'Brown' but not 'Brown/')
  • 3
2 Solutions
Ephraim WangoyaCommented:

select ID, REPLACE(Colors, ',', '/')  [Colors]
from MyTable
Give this a shot. Tested and works. Replace tbl with whatever your table name is.
        @SqlString =
                SELECT  Color + '/'
                FROM    tbl
                ORDER BY Color
                FOR XML PATH('')
FROM    tbl

PRINT @SqlString

Open in new window

johnkainnAuthor Commented:
Hi thank you. I cannot get the solution from ewangue to work. I just get one word.
In brd24gor. How is best to get rid of '/' at the end of the string?
Ephraim WangoyaCommented:
Sorry, I misunderstood your question
Ephraim WangoyaCommented:
Here is the correct solution

SELECT Stuff((Select  '/' + Color
              From    MyTable
              For xml Path(''))                        
              , 1, 1, '')

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now