Mini-Denormalize in Query Output

[Chris]

Hi,

I have two tables, an order and an order item table, and
I'd like to denormalize the first two order items of each
order into a query based on the order table.

E.g, simplistically:

I've got the parent table, Orders:
OrderNumber
OrderDate

The child table, OrderDetails:
OrderItemNo
OrderItemCode
OrderItemDesc

I have a parent record with OrderNo 1 OrderDAte 6/8/04,
with OrderItemCodes APPLE, CHERRY, and BLUEBERRY pie on
order in OrderDetails. The OrderItemNo values aren't
necessarily contiguous.

I want to write a query with output that looks like this,
showing the order and the first two items in
it "denormalized":

Ord# Date 1st Item 2nd Item
1 6/8/04 APPLE CHERRY

How can I do this? I.e., how can I grab the first order
detal item as a column, then the second order detail item
in a second column, all in one row with the order?

Chris

 

[Eric Cárdenas [MSFT]]

Hi,

I have two tables, an order and an order item table, and
I'd like to denormalize the first two order items of each
order into a query based on the order table.

E.g, simplistically:

I've got the parent table, Orders:
OrderNumber
OrderDate

The child table, OrderDetails:
OrderItemNo
OrderItemCode
OrderItemDesc

I have a parent record with OrderNo 1 OrderDAte 6/8/04,
with OrderItemCodes APPLE, CHERRY, and BLUEBERRY pie on
order in OrderDetails. The OrderItemNo values aren't
necessarily contiguous.

I want to write a query with output that looks like this,
showing the order and the first two items in
it "denormalized":

Ord# Date 1st Item 2nd Item
1 6/8/04 APPLE CHERRY

How can I do this? I.e., how can I grab the first order
detal item as a column, then the second order detail item
in a second column, all in one row with the order?

Chris

--------------------
Two steps:

1. Create a crosstab query for the Order details table:

TRANSFORM First(testOrderDetails.OrderDesc) AS FirstOfOrderDesc
SELECT testOrderDetails.OrderNumber
FROM testOrderDetails
GROUP BY testOrderDetails.OrderNumber
PIVOT testOrderDetails.OrderItemNo;

Results would be something like:

OrderNumber 1 2 3
1 apple peach banana
2 banana apple blueberry
3 cherry

2. Join the crosstab query with the Orders table using the OrderNumber:

SELECT testOrder.OrderNumber, testOrder.OrderDate, XtabOrderDetails.[1],
XtabOrderDetails.[2], XtabOrderDetails.[3]
FROM testOrder INNER JOIN XtabOrderDetails
ON testOrder.OrderNumber = XtabOrderDetails.OrderNumber;

Results would be something like:

OrderNumber OrderDate 1 2 3
1 12/12/2001 apple peach banana
2 1/2/2003 banana apple blueberry
3 3/1/2004 cherry

Hope this helps,