Solved

return string from selected items in column

Posted on 2011-03-11
5
399 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
Comment Utility

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

Assisted Solution

by:brd24gor
brd24gor earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, I misunderstood your question
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 150 total points
Comment Utility
Here is the correct solution


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

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now