Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • 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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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