Appending columns - horizantal dimention

Hi,
There are two queries as follow:
Original Query Result
select * from Customers
CustomerId Name OrderId
--
11 Mike 101
22 James 102
select * from OrderDetails
OrderId Product Quantity
---
101 100w speaker 1
101 Headphone 2
102 Blank CDs 1
I know this is an unusual approach to merge two result sets like below, but
what is the best way of getting the following result from the those two
queries above:
CustomerId Name OrderId Product Quantity Product
Quantity
----
--
11 Mike 101 100w speaker 1 Headphone 2
22 James 102 Blank CDs 1
Any query sample or suggestion will be appreciated.
Thanks
MehdiSelect *.C, *.OD
FROM Customers C(NOLOCK)
JOIN OrderDetails OD(NOLOCK) ON C.OrderID = OD.OrderID
Just my twist on it,
Adam Turner
"Mehdi" wrote:

> Hi,
> There are two queries as follow:
> Original Query Result
>
> select * from Customers
> CustomerId Name OrderId
> --
> 11 Mike 101
> 22 James 102
>
> select * from OrderDetails
> OrderId Product Quantity
> ---
> 101 100w speaker 1
> 101 Headphone 2
> 102 Blank CDs 1
>
> I know this is an unusual approach to merge two result sets like below, bu
t
> what is the best way of getting the following result from the those two
> queries above:
>
> CustomerId Name OrderId Product Quantity Product
> Quantity
> ----
--
> 11 Mike 101 100w speaker 1 Headphone
2
> 22 James 102 Blank CDs 1
>
> Any query sample or suggestion will be appreciated.
>
> Thanks
>
> Mehdi
>
>|||SELECT C.*, OD.*
FROM Customers C(NOLOCK)
JOIN OrderDetails OD(NOLOCK) ON C.OrderID = OD.OrderID
Just my twist on it,
Adam Turner
"Mehdi" wrote:

> Hi,
> There are two queries as follow:
> Original Query Result
>
> select * from Customers
> CustomerId Name OrderId
> --
> 11 Mike 101
> 22 James 102
>
> select * from OrderDetails
> OrderId Product Quantity
> ---
> 101 100w speaker 1
> 101 Headphone 2
> 102 Blank CDs 1
>
> I know this is an unusual approach to merge two result sets like below, bu
t
> what is the best way of getting the following result from the those two
> queries above:
>
> CustomerId Name OrderId Product Quantity Product
> Quantity
> ----
--
> 11 Mike 101 100w speaker 1 Headphone
2
> 22 James 102 Blank CDs 1
>
> Any query sample or suggestion will be appreciated.
>
> Thanks
>
> Mehdi
>
>|||Hi Adam,
Thanks for the reply, however you query is a simple join that gets the
results in vertical dimention.
I think I need some sort of a loop to create and append the columns from the
second result. I will post the final query to the group as soon as I find
a solution.
Thanks
Mehdi
"Adam Turner" <AdamTurner@.discussions.microsoft.com> wrote in message
news:DA06C785-1F8A-495A-B6B2-8FAB46D7B33A@.microsoft.com...
> SELECT C.*, OD.*
> FROM Customers C(NOLOCK)
> JOIN OrderDetails OD(NOLOCK) ON C.OrderID = OD.OrderID
> Just my twist on it,
> Adam Turner
> "Mehdi" wrote:
>|||On Tue, 13 Dec 2005 01:14:23 -0000, Mehdi wrote:

>Hi,
>There are two queries as follow:
>Original Query Result
>
>select * from Customers
>CustomerId Name OrderId
>--
>11 Mike 101
>22 James 102
>
>select * from OrderDetails
>OrderId Product Quantity
>---
>101 100w speaker 1
>101 Headphone 2
>102 Blank CDs 1
>
>I know this is an unusual approach to merge two result sets like below, but
>what is the best way of getting the following result from the those two
>queries above:
>
>CustomerId Name OrderId Product Quantity Product
>Quantity
>----
--
>11 Mike 101 100w speaker 1 Headphone 2
>22 James 102 Blank CDs 1
>
>Any query sample or suggestion will be appreciated.
Hi Mehdi,
Try if this works:
SELECT Der1.CustomerID, Der1.Name, Der1.OrderId,
Der1.Product, Der1.Quantity, Der2.Product, Der2.Quantity
FROM (SELECT a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity, COUNT(*) AS Rank
FROM Customers AS a
INNER JOIN orderDetails AS b
ON b.OrderId = a.OrderId
INNER JOIN orderDetails AS c
ON c.OrderId = b.OrderId
AND c.Product <= b.Product
GROUP BY a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity) AS Der1
LEFT JOIN (SELECT a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity COUNT(*) AS Rank
FROM Customers AS a
INNER JOIN orderDetails AS b
ON b.OrderId = a.OrderId
INNER JOIN orderDetails AS c
ON c.OrderId = b.OrderId
AND c.Product <= b.Product
GROUP BY a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity) AS Der2
ON Der2.CustomerID = Der1.CustomerID
AND Der2.Rank = Der1.Rank + 1
WHERE Der1.Rank % 2 = 1
(untested - see www.aspfaq.com/5006 if you prefer a testede reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Thanks for the advise, I havn't tried it yet, but I think it will work as
long as my number of columns are fixed. I have decided to manipulate the
dataset outside SQL using C# code to generate a new dataset based on number
of required number of columns as they are dynamic and change.
Thanks again.
Mehdi|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:froup1pla55vvh2tkf11i00cr528d70m65@.
4ax.com...
> On Tue, 13 Dec 2005 01:14:23 -0000, Mehdi wrote:
>
but
>----
--
2
> Hi Mehdi,
> Try if this works:
> SELECT Der1.CustomerID, Der1.Name, Der1.OrderId,
> Der1.Product, Der1.Quantity, Der2.Product, Der2.Quantity
> FROM (SELECT a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity, COUNT(*) AS Rank
> FROM Customers AS a
> INNER JOIN orderDetails AS b
> ON b.OrderId = a.OrderId
> INNER JOIN orderDetails AS c
> ON c.OrderId = b.OrderId
> AND c.Product <= b.Product
> GROUP BY a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity) AS Der1
> LEFT JOIN (SELECT a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity COUNT(*) AS Rank
> FROM Customers AS a
> INNER JOIN orderDetails AS b
> ON b.OrderId = a.OrderId
> INNER JOIN orderDetails AS c
> ON c.OrderId = b.OrderId
> AND c.Product <= b.Product
> GROUP BY a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity) AS Der2
> ON Der2.CustomerID = Der1.CustomerID
> AND Der2.Rank = Der1.Rank + 1
> WHERE Der1.Rank % 2 = 1
Ugggh....
Have you forgotten about RAC :)
www.rac4sql.net