?
Solved

return string from selected items in column

Posted on 2011-03-11
5
Medium Priority
?
416 Views
Last Modified: 2012-05-11
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
Comment
Question by:johnkainn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35109557

select ID, REPLACE(Colors, ',', '/')  [Colors]
from MyTable
0
 
LVL 3

Assisted Solution

by:brd24gor
brd24gor earned 400 total points
ID: 35109633
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
 

Author Comment

by:johnkainn
ID: 35109921
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35110345
Sorry, I misunderstood your question
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 600 total points
ID: 35110389
Here is the correct solution


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

Open in new window

0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question