Hi all,
I have 3 tables named Location, Products and Orders
Location table columns are LocationIDand LocationName
Products table columns are ProductId, ProductName
Orders table columns are ProductId, LocationID, OrderedQty
the location table sample data is
+----------------------------+
|LocationID | LocationName |
+----------------------------+
|1 | Location1 |
|2 | Location2 |
|... |
|N | LocationN |
+-----------------------------+
Products Table Sample data is
+----------------------------+
|ProductID | ProductName |
+----------------------------+
|1000 | xxxxxx |
|1001 | yyyyy |
|... |
|9999 | nnnnn |
+-----------------------------+
Orders Table Sample data is
+------------------------------------------+
|ProductID | LocationId | OrderedQty |
+------------------------------------------+
|1000 | 1 | 10 |
|1000 | 2 | 00 |
|1000 | 3 | 20 |
|1001 | 1 | 15 |
|1001 | 2 | 45 |
|1001 | 3 | 05 |
|9999 | 1 | 00 |
|9999 | 2 | 90 |
|9999 | 3 | 50 |
|.. | | |
+------------------------------------------+
I want to show the Orderd quantity of the each locations in columnwise
like
+--------------------------------------------------------------------+
|ProductId | ProductName | Location1 | Location2 | Location3 |
+--------------------------------------------------------------------+
|1000 | xxxxx | 10 | 0 | 20 |
|1001 | yyyyy | 15 | 45 | 05 |
|... | | | | |
|999 | nnnnn | 0 | 90 | 50 |
+--------------------------------------------------------------------+
How to write sql query for the above output in sql server 2000.
Thanks in advance
|