We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Sql Query Question (How to Combine several rows into one row)
Posted on 2009-05-13
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
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
[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
- +1
5 Comments
use the FOR XML PATH() syntax:
http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html
http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html
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
,MAX (ShipDate)
FROM urTable b
GROUP BY ItemNumber
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Course of the Month8 days, 8 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.