Showing total five records even if Null

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

Transact-SQL (2005)

Showing total five records even if Null

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-24 : 11:08:25

I have Orders table and Order_Items table.

Orders Table

Order_id
Total

Order_Items Table

order_item_id
product_id
quantity
Order_Id

Even if the order has three items I want to display five items, two of them will be NULL

Result

Order_Id Product_Id1 Quantity1 Product_Id2 Quantity2 Product_Id3 Quantity3 Product_Id4 Quantity4 Product_Id5 Quantity5

2533 5011 2 5021 1 5023 1 NULL NULL NULL NULL

select
oi.order_id
,oi.quantity
,oi.product_id
from Orders o
LEFT JOIN
(SELECT ioi.order_id
,ioi.quantity
,ioi.product_id
FROM
Order_Items ioi) oi ON o.order_id=oi.order_id
LEFT JOIN Order_Tenders ot ON o.order_id = ot.order_id
LEFT JOIN Tenders t ON t.tender_id=ot.tender_id

How can I modify the query?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 11:10:54

This doesn't make a lot of sense. Whay are you trying to do this?

EDIT: I see you have changed your expected output...try this

select 
o.order_id
,MAX(case when oi.seq = 1 then oi.product_id else null end) as Product_Id1 
,max(case when oi.seq = 1 then oi.quantity else null end) as Quantity1
,MAX(case when oi.seq = 2 then oi.product_id else null end) as Product_Id2 
,max(case when oi.seq = 2 then oi.quantity else null end) as Quantity2
.
.
.
.
from Orders o
LEFT JOIN 
(SELECT row_number() over(partition by order_id order by product_id ) as seq
,order_id
,quantity
,product_id
FROM Order_Items) oi ON o.order_id=oi.order_id
group by o.order_id

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-24 : 11:16:08

Result

Order_Id Product_Id1 Quantity1 Product_Id2 Quantity2 Product_Id3 Quantity3 Product_Id4 Quantity4 Product_Id5 Quantity5
2533 5011 2 5021 1 5023 1 NULL NULL NULL NULL
2534 5014 1 NULL NULL NULL NULL NULL NULL NULL NULL

There are two order. Order Number 2533 with 3 products and 2534 with one products. But I need five product count, rest of them will be NULL

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-24 : 11:18:57

How can I get the result. Table variables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:23:11

[code]
SELECT OrderID,
MAX(CASE WHEN Seq=1 THEN product_id ELSE NULL END) AS Product_ID1,
MAX(CASE WHEN Seq=1 THEN quantity ELSE NULL END) AS Quantity1,
MAX(CASE WHEN Seq=2 THEN product_id ELSE NULL END) AS Product_ID2,
MAX(CASE WHEN Seq=2 THEN quantity ELSE NULL END) AS Quantity2,
MAX(CASE WHEN Seq=3 THEN product_id ELSE NULL END) AS Product_ID3,
MAX(CASE WHEN Seq=3 THEN quantity ELSE NULL END) AS Quantity3,
MAX(CASE WHEN Seq=4 THEN product_id ELSE NULL END) AS Product_ID4,
MAX(CASE WHEN Seq=4 THEN quantity ELSE NULL END) AS Quantity4,
MAX(CASE WHEN Seq=5 THEN product_id ELSE NULL END) AS Product_ID5,
MAX(CASE WHEN Seq=5 THEN quantity ELSE NULL END) AS Quantity5
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Order_Id ORDER BY product_id) AS Seq,
product_id,
quantity,
Order_Id
FROM YourTable
)c
GROUP BY OrderID
[/code]

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 11:25:24

quote:
Originally posted by mapidea

How can I get the result. Table variables

I have edited my previous reply..pls see that

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-24 : 11:26:11

Thanks a lot Vijay. Thats the result.

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-24 : 11:26:53

Thanks a lot Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:27:05

quote:

Originally posted by vijayisonly

This doesn't make a lot of sense. Whay are you trying to do this?

EDIT: I see you have changed your expected output...try this

select 
o.order_id
,MAX(case when oi.seq = 1 then oi.product_id else null end) as Product_Id1 
,max(case when oi.seq = 1 then oi.quantity else null end) as Quantity1
,MAX(case when oi.seq = 2 then oi.product_id else null end) as Product_Id2 
,max(case when oi.seq = 2 then oi.quantity else null end) as Quantity2
.
.
.
.
from Orders o
LEFT JOIN 
(SELECT row_number() over(partition by order_id order by product_id ) as seq
,order_id
,quantity
,product_id
FROM Order_Items) oi ON o.order_id=oi.order_id
group by o.order_id

You actually dont need the order table here at all as OP doesnt want any order details to be displayed as per sample output

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 11:29:43

But Maybe if there is an order_id in Orders table but no corresponding record in Order_Items table? Not sure if that's possible though...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:32:45

quote:
Originally posted by vijayisonly

But Maybe if there is an order_id in Orders table but no corresponding record in Order_Items table? Not sure if that's possible though...

An order without items? is that a valid order

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 11:34:53

quote:
Originally posted by visakh16

quote:
Originally posted by vijayisonly

But Maybe if there is an order_id in Orders table but no corresponding record in Order_Items table? Not sure if that's possible though...

An order without items? is that a valid order

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

There's a table with just order_id as the field..so I wud think anything is valid

...but seriously..I dont think the 'Orders' table is required here..unless he is getting some other info from that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:37:16

yeah...that was my point

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources