[RESOLVED] is there any query string that can do this?

[sphericalx]

This are the tables examples.

ServicesAndProductsTable
productID, productDescription, type, productCategory
1000, product1, product, skincare
1001, product2, service, facial
1002, product3, product, skincare
1003, product4, service, manicure

ReceiptSummaryTable
ReceiptID, clientID, totalAmt, employeeName
11000, 100, $10, James
11001, 110, $5, James
11002, 150, $15, Sandy

ReceiptRowsTable
ReceiptID, productID, type, amt
11000, 1000, product, $5
11000, 1003, service, $5
11001, 1002, product, $5
11002, 1003, service, $5
11002, 1001, service, $10

==============================================
Expected output:

employee ID, employee Name, productTotal, facialTotal, manicureTotal
1, James, $10, $0, $5
6, Sandy, $0, $10, $5

is there a query which can do this?
productTotal is calculated from the ReceiptSummaryTable where type = product

As for facialTotal and manicureTotal will be slightly complicated.
facialTotal is calculated from ReceiptSummaryTable where type = product AND category = facial in ServicesAndProductsTable.
likewise for manicureTotal.

anyway, i'm using sql server 2000.

[RhinoBull]

Your "Expected output" looks like crosstab report. You may experiment with it using ms access database (you'll need to link or import some tables) or perhaps using crystal reports.
Links below may get you started:
http://www.crystalreportsbook.com/Cr....asp?Page=10_1
http://www.blueclaw-db.com/report_dy...stab_field.htm
http://aspalliance.com/1041_creating...rystal_reports

[sphericalx]

i'm working on a window application thus i need the result to be returned in such a way therefore i can pass it over to my window application to be shown on a datagridview.

[RhinoBull]

Well, there is a way but I would first recommend dropping the summary table (you really don't need it) and adding Employees and Clients (if you don't already have those).
See attached image.

[sphericalx]

i had my employee and client table all along.

anyway, manage to get the solution to it from someone else. thanks alot for ur help too!! ^^

for the benefits of whoever that might come across with this problem, below is the solution

SELECT RST.employeeName,
SUM(CASE WHEN SPT.type='product' THEN RRT.amt ELSE 0 END) AS productTotal,
SUM(CASE WHEN SPT.productCategory='facial' THEN RRT.amt ELSE 0 END) AS facialTotal,
SUM(CASE WHEN SPT.productCategory='manicure' THEN RRT.amt ELSE 0 END) AS manicureTotal
FROM ReceiptSummaryTable RST
INNER JOIN ReceiptRowsTable RRT
ON RRT.ReceiptID = RST.ReceiptID
INNER JOIN ServicesAndProductsTable SPT
ON SPT.productID = RRT.productID AND SPT.type = RRT.type
GROUP BY RST.employeeName