Solved

SQL Grouping Query

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

Backup Your Microsoft Windows Server®

Backup 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

Suggested Solutions

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…
In this article I will describe the Backup & Restore 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 video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now