Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Combine row results based on multiple criteria

Author  Topic 

poison88
Starting Member

1 Post

Posted - 2012-06-15 : 13:00:37
I have a query that retrieves a large result set—200,000+ records—with correct data but separated into multiple rows. I need to combine some of these rows together by 2 field criteria. This is an example of what I have and what I need:
Manufactured_Item Operation Task Text
86252-01 10 250 First part of text
86252-01 10 250 and the 2nd part of text
86252-01 10 250 then remaining text.
86252-01 20 400 NULL
86252-01 30 250 Text for Operation 30

Based on the Manufactured_Item number and Operation—if the same—I need to have a unique row with the text combined (concatenated); I need it to look like this:
Manufactured_Item Operation Task Text
86252-01 10 250 First part of text and the 2nd part of text then remaining text.
86252-01 20 400 NULL
86252-01 30 250 Text for Operation 30

How can I accomplish this through a Management Studio Query?

Thank you very much!


Poison88

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 11:58:59
[code]
SELECT Manufactured_Item,
Operation,
Task,
STUFF((SELECT '' + FROM Table
WHERE Manufactured_Item = t.Manufactured_Item
AND Operation = t.Operation
AND Task = t.Task
FOR XML PATH('')),1,1,'') AS List
FROM (SELECT DISTINCT Manufactured_Item,Operation,Task FROM Table) t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -