How to merge multi rows to single row?

[daniyalnawaz]

Hi,

I want to merge data from multi rows to one row.
For example,
I have two table one is 'ORDER' and one is 'ORDERDETAIL'.
What I want to do is when I want to get orders against 'user'...
I want to get 'ORDER' table INNER JOINED with 'ORDERDETAIL' but with TOTAL added and merged in a sigle row instead of multiple rows.
I want to generate this function in Stored Procedure in SQL 2005.
Can you help me about this?



1. ORDERDETAIL Table
OrderID......Price.....Quantity
A..................10............1
A..................14............2
B..................15............3
C..................20............4
C..................23............5
C..................24............6

2. RESULT TABLE (Output)
OrderID.........Total
A..................(10 * 1) + (14 * 2)
B..................(15 * 3)
C..................(20*4) + (23*5) + (24*6)

[azimmer]

Hi,

I want to merge data from multi rows to one row.
For example,
I have two table one is 'ORDER' and one is 'ORDERDETAIL'.
What I want to do is when I want to get orders against 'user'...
I want to get 'ORDER' table INNER JOINED with 'ORDERDETAIL' but with TOTAL added and merged in a sigle row instead of multiple rows.
I want to generate this function in Stored Procedure in SQL 2005.
Can you help me about this?



1. ORDERDETAIL Table
OrderID......Price.....Quantity
A..................10............1
A..................14............2
B..................15............3
C..................20............4
C..................23............5
C..................24............6

2. RESULT TABLE (Output)
OrderID.........Total
A..................(10 * 1) + (14 * 2)
B..................(15 * 3)
C..................(20*4) + (23*5) + (24*6)

SELECT o.OrderID, sum(d.Price*d.Quantity)
FROM Order o INNER JOIN OrderDetail d ON o.OrderId=d.OrderId
GROUP BY o.OrderID
 

[Jim Doherty]

Hi,

I want to merge data from multi rows to one row.
For example,
I have two table one is 'ORDER' and one is 'ORDERDETAIL'.
What I want to do is when I want to get orders against 'user'...
I want to get 'ORDER' table INNER JOINED with 'ORDERDETAIL' but with TOTAL added and merged in a sigle row instead of multiple rows.
I want to generate this function in Stored Procedure in SQL 2005.
Can you help me about this?



1. ORDERDETAIL Table
OrderID......Price.....Quantity
A..................10............1
A..................14............2
B..................15............3
C..................20............4
C..................23............5
C..................24............6

2. RESULT TABLE (Output)
OrderID.........Total
A..................(10 * 1) + (14 * 2)
B..................(15 * 3)
C..................(20*4) + (23*5) + (24*6)

Your output column is confusing me in that I'm not sure whether you want to see that display 'literally' or whether you merely want it summing. To sum it is relatively easy but to display it 'literally' as you have it requires extra so I am going to assume the latter

Firstly Create this function

 
CREATE FUNCTION dbo.UDF_OrderDetail
( @OrderID char(1) )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @OrderDetail varchar(1000), @Delimiter char
SET @Delimiter = '+'
SELECT @OrderDetail = COALESCE(@OrderDetail + @Delimiter, '') + strPQ FROM 
(SELECT     OrderID, Price, Quantity, '(' + LTRIM(STR(Price)) + ' * ' + LTRIM(STR(Quantity)) + ')' AS strPQ
FROM         dbo.OrderDetail where OrderID=@OrderID) derived
WHERE OrderID=@OrderID
RETURN ( SELECT REPLACE(@OrderDetail,')+(',') + (') AS [Order_Detail])
END
 

Then to use it in a query or view call it like this

 
SELECT DISTINCT OrderID, dbo.UDF_OrderDetail(OrderID) AS Total
FROM         dbo.OrderDetail
 

it gives you the two column layout (OrderID and Total) output you required using only the order details table

Not sure if this helps you or not as summing seems the obvious but I can see a case where you 'might' want to see the breakdown in a continuous line like that sooooo

Regards

Jim

[seharmks]

SELECT o.OrderID, sum(d.Price*d.Quantity)
FROM Order o INNER JOIN OrderDetail d ON o.OrderId=d.OrderId
GROUP BY o.OrderID
 

Above query works well but I am facing some other problems.

I have ProductName, ProductPrice, ProductSize columns as well in OrderDetail table. I also want these columns too but when I select these columns, it gives an error of GROUP BY clause of each newely selected column.

Is there any solution. Following is the sample solution that I want

OrderID----------Total----------ProductName----------ProductPrice----------ProductSize
A-------------(25*2)+(10+1)------------ABC-------------------$10-----------------------Large
B-------------(10*1)+(1*2)+(2*1)------DEF--------------------$5------------------------Small


I hope, above result sample clears the requirement.

Thanx in advance

[seharmks]

SELECT o.OrderID, sum(d.Price*d.Quantity)
FROM Order o INNER JOIN OrderDetail d ON o.OrderId=d.OrderId
GROUP BY o.OrderID
 

Above query works well but I am facing some other problems.

I have ProductName, ProductPrice, ProductSize columns as well in OrderDetail table and Discount in Order table. I also want these columns too but when I select these columns, it gives an error of GROUP BY clause of each newely selected column.

Is there any solution. Following is the sample solution that I want

OrderID---------Total----------------ProductName---------ProductPrice---------ProductSize
A------(25*2)+(10+1)-Discount------------ABC-------------------$10---------------------------Large
B------(10*1)+(1*2)+(2*1)-Discount------DEF-------------------$5----------------------------Small


I hope, above result sample clears the requirement.

Thanx in advance

[azimmer]

Above query works well but I am facing some other problems.

I have ProductName, ProductPrice, ProductSize columns as well in OrderDetail table and Discount in Order table. I also want these columns too but when I select these columns, it gives an error of GROUP BY clause of each newely selected column.

Is there any solution. Following is the sample solution that I want

OrderID---------Total----------------ProductName---------ProductPrice---------ProductSize
A------(25*2)+(10+1)-Discount------------ABC-------------------$10---------------------------Large
B------(10*1)+(1*2)+(2*1)-Discount------DEF-------------------$5----------------------------Small


I hope, above result sample clears the requirement.

Thanx in advance

If ProductName, ProductPrice and ProductSize are in the Detail table then you need to define some algorithm to "aggregate" them. If they are in the Order table you only need to "Group by" each of them as well:

SELECT o.OrderID, sum(d.Price*d.Quantity) as Total, o.ProductName, o.ProductPrice, o.ProductSize
FROM Order o INNER JOIN OrderDetail d ON o.OrderId=d.OrderId
GROUP BY o.OrderID, o.ProductName, o.ProductPrice, o.ProductSize
 

Discount is similar to the original query if it is in the Detail table:

SELECT o.OrderID, sum(d.Price*d.Quantity-d.Discount)
FROM Order o INNER JOIN OrderDetail d ON o.OrderId=d.OrderId
GROUP BY o.OrderID
 

[Bangaru]

Hi Jim,

I am not sure whether I should open a new thread for my problem...
since I found this post to be related with my doubts I reply here

In my case it is like I select a particular column with where conditions, it returns 8 rows like:

Result set

Defect
*******
Test1
Test2
Test3

I need the result in single row like as below

Defect
*******
Test1,Test2,Test3

where Defect is the particular column I am retrieving.
Can u please help me in this?

[javedameen]

Dear Daniyal
To get OrderId wise sumup result from OrderDetail table, use this

Select OrderId,SUM(NVL(Price,0)*NVL(Quantity,0))
From OrderDetail
[Where <condition...date,code,range>]
Group by OrderId;

Regards
Javed Ameen
-------------------------------------------------------------------------------------

Hi,

I want to merge data from multi rows to one row.
For example,
I have two table one is 'ORDER' and one is 'ORDERDETAIL'.
What I want to do is when I want to get orders against 'user'...
I want to get 'ORDER' table INNER JOINED with 'ORDERDETAIL' but with TOTAL added and merged in a sigle row instead of multiple rows.
I want to generate this function in Stored Procedure in SQL 2005.
Can you help me about this?



1. ORDERDETAIL Table
OrderID......Price.....Quantity
A..................10............1
A..................14............2
B..................15............3
C..................20............4
C..................23............5
C..................24............6

2. RESULT TABLE (Output)
OrderID.........Total
A..................(10 * 1) + (14 * 2)
B..................(15 * 3)
C..................(20*4) + (23*5) + (24*6)