[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL How do I concatenate strings from a column into a single row

Posted on 2009-04-10
4
Medium Priority
?
982 Views
Last Modified: 2012-05-06
The  column ProductType.searchtext in this query can have multiple values. I need to join the values together separated by a comma to insure there is always one row of data. I have done this in separate queries but cannot put it all together.
------------ Query One --------------
 
SELECT endshare.orderno, endshare.ship_via, endshare.nvalue, endshare.bill_weigh, endshare.sname, endshare.scompany, endshare.saddr1, endshare.saddr2, endshare.scity, endshare.sstate, endshare.szipcode, endshare.scountry, endshare.sphone1, endshare.semail, endshare.usi_status, endshare.ship_date, endshare.BOX_ID, endshare.ORDERNO, ProductType.searchtext
 
FROM endshare 
 
join items on items.orderno = endshare.orderno
join stock on items.item = stock.number
join ProductType on stock.assoc = ProductType.prodtype
 
WHERE endshare.orderno=318850
 
 
 
------------ Query Two --------------
DECLARE @searchtext VARCHAR(1024) 
 
SELECT 
    @searchtext = COALESCE(@searchtext + ',', '') + searchtext 
FROM 
    ProductType
 
SELECT ProductType = @searchtext

Open in new window

0
Comment
Question by:ubsmail
3 Comments
 
LVL 3

Expert Comment

by:GarthSnyder
ID: 24120663
You will need to use a user-defined function, as you have done. Here is an example of combining the concatenation with other query results. See the second post, by TG.

This feature is built into MySQL in the form of the GROUP_CONCAT() aggregate function, so Googling for GROUP_CONCAT and "MS Sql" brings up a lot of good hits.

I would include exact code for you but I am not actually an MS Sql user and don't have a test database available.
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 1000 total points
ID: 24121046
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24157447.html

I had a similar question.

Essentially I ended up with code like this ...
CREATE FUNCTION [dbo].[ListContractsForSingleReport](@i_ReportID INT, @s_Separator VARCHAR(MAX) = ',')
RETURNS VARCHAR(MAX)
AS
BEGIN
        DECLARE @s_List VARCHAR(MAX)
        SELECT @s_List = ''
        SELECT @s_List = @s_List + @s_Separator +  CAST(ContractID AS VARCHAR)
        FROM BANDIT.dbo.ContractReports
        WHERE @ i_ReportID  = ReportID 
        RETURN SubString(@s_List, 2, Len(@s_List))
END

Open in new window

0
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 1000 total points
ID: 24121168
Use an XML trick like this one from before http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24310874.html
I believe the code below is correctly applied to your query.
SELECT endshare.orderno, endshare.ship_via, endshare.nvalue, endshare.bill_weigh, endshare.sname, endshare.scompany, endshare.saddr1, endshare.saddr2, endshare.scity, endshare.sstate, endshare.szipcode, endshare.scountry, endshare.sphone1, endshare.semail, endshare.usi_status, endshare.ship_date, endshare.BOX_ID, endshare.ORDERNO, ProductType = REPLACE((SELECT Names AS [data()]
                   FROM ProductType
                   WHERE stock.assoc = ProductType.prodtype
                   FOR XML PATH ('')), ' ', ',')
 FROM endshare 
join items on items.orderno = endshare.orderno
join stock on items.item = stock.number
join ProductType on stock.assoc = ProductType.prodtype
WHERE endshare.orderno=318850

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 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