?
Solved

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

Posted on 2009-04-10
4
Medium Priority
?
978 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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