Solved

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

Posted on 2009-04-10
4
963 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
4 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 250 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 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 250 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 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