Solved

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

Posted on 2009-04-10
4
962 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

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 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