Solved

SQL Grouping Query

Posted on 2008-06-16
2
210 Views
Last Modified: 2010-04-21
Here's my data.

Shipment             SO
84773                 337516
84775                 337526
84775                 341902
84775                 345099
84776                 356243
84776                 357100


I need a SQL query to group all of the SO's into one field per shipment. I know that's not a great practice, but I need this for a report.

For example:

Shipment              SO
84773                   337516
84775                   337526 341902 345099
84776                   356243 357100

Thank you
0
Comment
Question by:kstahl
[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
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 21796513
you will need a user-defined function:
CREATE FUNCTION dbo.ConcatSO(@Shipment int)
returns VARCHAR(MAX)
AS
BEGIN
  DECLARE @res VARCHAR(MAX)
  SELECT @res = COALESCE(@res + ' ', '') + CAST(SO AS VARCHAR(100))
    FROM yourtable
   WHERE shipment = @shipment
  RETURN @res
END 

and your query will be like this: 
SELECT t.shipment, dbo.ConcatSO(t.Shipment) SO_list
  FROM yourtable t
 GROUP BY t.shipment

Open in new window

0
 

Author Closing Comment

by:kstahl
ID: 31467719
Perfect...thank you!
0

Featured Post

Independent Software Vendors: 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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

735 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