SQL Grouping Query

Posted on 2008-06-16
Medium Priority
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
Question by:kstahl
LVL 143

Accepted Solution

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

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


Author Closing Comment

ID: 31467719
Perfect...thank you!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

622 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