Solved

Sql Query Question (How to Combine several rows into one row)

Posted on 2009-05-13

1 solution

873 Views

Last Modified: 2012-05-06

I have two tables one table contains order information OrderNumber, ItemNumber, ItemDescription and things like that. The other table contains Shipment information, Box Number
Date Shipped and Time Shipped. If a Item in Table one has been shipped in multiple boxes say 3 for example I do not want to get three rows returned with the only difference being a box number I want to get One row returned and it contain all three box numbers and the Latest Date Shipped

Here is what I get now

Item Number Box Number Date Shipped
55532 234 4/3/2009
55532 548 4/12/2009
55532 762 5/1/2009

Here is what I want to get

ItemNumber BoxNumber DateShipped
55532 234, 548, 762 5/152009

How do I write my query statement to get the results that I want

I am using Microsoft Sql 2008

Comment

Question by:WesleyAlgee

[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

Join The Community

5 Comments

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]

ID: 243788482009-05-13

use the FOR XML PATH() syntax:
http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html

LVL 75

Accepted Solution

by:Aneesh Retnakaran

Aneesh Retnakaran earned 250 total points

ID: 243788912009-05-13

SELECT ItemNumber, Box = (SELECT cast(BoxNumber as varchar ) +',' FROM urTable t where t.ItemNumber = b.itemNumber for XML path('') )
,MAX (ShipDate)
FROM urTable b
GROUP BY ItemNumber

LVL 41

Expert Comment

by:ralmada

ID: 243796302009-05-13

Please give this a try. Change table names accordingly.

SELECT 
	a.ItemNumber
	BoxNumber = REPLACE( 
	( 
	SELECT 
	BoxNumber AS [data()] 
	FROM 
	shipmenttable c 
	WHERE 
	c.ItemNumber = b.ItemNumber
	ORDER BY 
	c.ItemNumber 
	FOR XML PATH ('') 
	), ' ', ','),
 
	Max(b.DateShipped)
FROM 
Ordertable a
Inner join shipmenttable b on a.ItemNumber = b.ItemNumber
group by a.ItemNumber

Open in new window

Author Comment

by:WesleyAlgee

ID: 243800422009-05-13

ok I will give these a try I will not be able to work on it again until tomorrow

Author Comment

by:WesleyAlgee

ID: 245601652009-06-05

I am sorry it took me so long to respond something critical came up that I had to give my attention to

Featured Post

Question has a verified solution.

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

Parse JSON into a hierarchical table

Article by: Guy

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.

How to replicate SQL 2008 database to SQL 2014 by using Initialize from Backup.

Article by: lcohan

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

Dealing With Dates: Manipulating Dates (SQL Server)

Video by: Steve

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.