Solved

SQL Grouping Query

Posted on 2008-06-16
2
215 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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