• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • 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/')
0
johnkainn
Asked:
johnkainn
  • 3
2 Solutions
 
Ephraim WangoyaCommented:

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

PRINT @SqlString

Open in new window

0
 
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?
0
 
Ephraim WangoyaCommented:
Sorry, I misunderstood your question
0
 
Ephraim WangoyaCommented:
Here is the correct solution


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

Open in new window

0
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

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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