Not sure how to construct - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Not sure how to construct

Author

Topic

Felmonk
Starting Member

4 Posts

Posted - 2010-09-17 : 11:54:32

Hello - I'm expected to manage lots of data at my job with no training in SQL. I’m learning on the fly and appreciate any help. I am pulling data from two tables - matching requests from a "requests" table to offers from an "offers" table. My statement is basically this:

Select Requests.ReqCompany, Requests.ItemNo, Requests.Qty, Offers.OfferNo where Requests.ItemNo=Offers.ItemNo and Offers.Qty>=Requests.Qty;

I’m returning multiple offers for each request, and the result is I have rows that are duplicates in regard to the Requests data, but with different Offer data. What I would like to do is return one row for each request, but with separate columns for the offer numbers so that the columns would display like this: ReqCompany, ItemNo, Qty, OfferNo1, OfferNo2, OfferNo3, etc.

Is it possible to do this? My ultimate goal is to insert the return into a new table I'll create for these matches. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 11:56:21

are you sure of number of offers for a request? i.e are they static or can they vary?

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

Felmonk
Starting Member

4 Posts

Posted - 2010-09-17 : 11:57:56

They can vary but there will be no more than 15. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 11:59:05

are you using sql 2005?

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

Felmonk
Starting Member

4 Posts

Posted - 2010-09-17 : 12:02:35

Yes - MS Server Server Management Studio Express 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 12:13:31

[code]
SELECT ReqCompany, ItemNo, Qty,
MAX(CASE WHEN Seq=1 THEN OfferNo ELSE NULL END) AS OfferNo1,
MAX(CASE WHEN Seq=2 THEN OfferNo ELSE NULL END) AS OfferNo2,
MAX(CASE WHEN Seq=3 THEN OfferNo ELSE NULL END) AS OfferNo3,
...
MAX(CASE WHEN Seq=15 THEN OfferNo ELSE NULL END) AS OfferNo15
FROM
(
SELECT Requests.ReqCompany, Requests.ItemNo, Requests.Qty, Offers.OfferNo ,
ROW_NUMBER() OVER(PARTITION BY Requests.ReqCompany, Requests.ItemNo, Requests.Qty ORDER BY Offers.OfferNo) AS Seq
FROM...
where Requests.ItemNo=Offers.ItemNo and Offers.Qty>=Requests.Qty;
)t
GROUP BY ReqCompany, ItemNo, Qty
[/code]

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

Felmonk
Starting Member

4 Posts

Posted - 2010-09-17 : 13:15:29

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 13:16:22

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources