Solved

return string from selected items in column

Posted on 2011-03-11
5
403 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
  • 3
5 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35109557

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

Assisted Solution

by:brd24gor
brd24gor earned 100 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:ewangoya
ID: 35110345
Sorry, I misunderstood your question
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 150 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

791 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