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.
| Author |
Topic |
|
TheMick15
Starting Member
4 Posts |
 Posted - 2008-10-16 : 16:53:39
|
| Ok, I am creating a report in Reporting Services, and I need to get my stored procedure right. I have a table that has an OrderID and has several schedule items for that order. So each order can have a Deliver or a Pickup. The problem is, one OrderID can have a delivery and a pickup then another delivery and another pickup. Each particular delivery or pickup has its own unique OrderDetailID So I have columns likeOrderID OrderDetailID Operation Location 1 1 Del America 1 2 Pick China 1 3 Del China 2 4 Del America 2 5 Pick China I need to create a temporary table with the OrderID and 4 Delivery columns (Delivery1, Delivery2, Delivery3, Deliver4) and 4 Pickups (Pickup1, Pickup2, Pickup3, Pickup4) for each order. 4 is the maximum number of deliveries or pickups any order can have, and an order can have just 1 delivery or 1 pickup.The idea is I want to have columns in my report that are like, OrderID Delivery1 Delivery2 Delivery3 Delivery4 Pickup1 Pickup2 Pickup3 Pickup4 with the locations of each of those deliveries/pickups underneath. I know this is totally confusing! Any ideas? |
|
|
TheMick15
Starting Member
4 Posts |
Posted - 2008-10-16 : 16:57:47
|
| The Deliveries and Pickups can just be ordered by time and put into the table in that order if that detail helps |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-10-17 : 06:08:56
|
| Can you send structure of each table? |
|
|
|
nakuvalekar
Starting Member
7 Posts |
Posted - 2008-10-21 : 03:11:40
|
| Try cross-tab reports in SQL server. It may remove the temp table requirement also. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-21 : 03:42:53
|
| [code]SELECT OrderID,MAX(CASE WHEN Seq=1 AND Operation ='Del' THEN Location ELSE NULL END) AS Delivery1,MAX(CASE WHEN Seq=2 AND Operation ='Del' THEN Location ELSE NULL END) AS Delivery2,MAX(CASE WHEN Seq=3 AND Operation ='Del' THEN Location ELSE NULL END) AS Delivery3,MAX(CASE WHEN Seq=4 AND Operation ='Del' THEN Location ELSE NULL END) AS Delivery4,MAX(CASE WHEN Seq=1 AND Operation ='Pick' THEN Location ELSE NULL END) AS Pickup1,MAX(CASE WHEN Seq=2 AND Operation ='Pick' THEN Location ELSE NULL END) AS Pickup2,MAX(CASE WHEN Seq=3 AND Operation ='Pick' THEN Location ELSE NULL END) AS Pickup3,MAX(CASE WHEN Seq=4 AND Operation ='Pick' THEN Location ELSE NULL END) AS Pickup4FROM(SELECT ROW_NUMBER() OVER(PARTITION BY OrderID,Operation ORDER BY OrderDetailID) AS Seq,*FROM YourTable)tGROUP BY OrderID[/code] |
|
|
|
|
|
|
|
|
Temporary Table, I'm Stumped!