Solved

SQL Grouping Query

Posted on 2008-06-16
2
193 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
2 Comments
 
LVL 142

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

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 48
SQL Server 208R2 not recognizing DBF file in linked Server 11 57
Isolation level in SQL server 3 50
sql query help 2 53
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…
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.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 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