May 8, 2008 at 1:23 am
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
May 9, 2008 at 3:57 am
you can try these statement ::
SELECT Orders.productid , Product.ProductName , Location1.OrderedQty , Location2.OrderedQty , Location3.OrderedQty
FROM Orders INNER JOIN Product ON Orders.ProductID = Product.ProductID
LEFT OUTER JOIN
(
SELECT productid , Locationid , OrderedQty
FROM dbo.Orders
WHERE LocationId = 1
) AS Location1 ON Orders.ProductID = Location1.productid
LEFT OUTER JOIN
(
SELECT productid , Locationid , OrderedQty
FROM dbo.Orders
WHERE LocationId = 2
) AS Location2 ON Orders.ProductID = Location2.productid
LEFT OUTER JOIN
(
SELECT productid , Locationid , OrderedQty
FROM dbo.Orders
WHERE LocationId = 3
) AS Location3
ON Orders.ProductID = Location3.productid
GO
Mina G Galil
+20105320253
May 9, 2008 at 6:15 am
Hi
my location table has N number of records.
May 9, 2008 at 10:33 am
I can not get the results in one row as you have shown in a simplified manner.
From BOL:
The result sets combined using UNION must all have the same structure.
They must have the same number of columns,
and the corresponding result set columns must have compatible data types
Hopefully this will get you started
SELECT Orders.ProductId, Products.ProductName, Orders.OrderQty, Location.LocationName
FROM Orders INNER JOIN
Location ON Orders.LocationId = Location.LocationId INNER JOIN
Products ON Orders.ProductId = Products.Productid
which gives:
ProductIdProductName OrderQty LocationName
1000 xxxxxx 10 Loc1
1000 xxxxxx 0 loc2
1000 xxxxxx 20 loc3
1001 yyyy 15 Loc1
1001 yyyy 45 loc2
1001 yyyy 5 loc3
9999 nnnnn 0 Loc1
9999 nnnnn 90 loc2
9999 nnnnn 50 loc3
May 9, 2008 at 10:44 am
jprakasam (5/9/2008)
Himy location table has N number of records.
you'll have to specify a fixed number of locations. otherwise, you're trying to write a query that may have 8 columns sometimes and 80 columns the next time.
select P.productId, min(P.productName),
sum( case O.locationId when 1 then O.orderedQty end ) as qtyLocation1,
sum( case O.locationId when 2 then O.orderedQty end ) as qtyLocation2,
sum( case O.locationId when 3 then O.orderedQty end ) as qtyLocation3,
...
sum( case O.locationId when {N} then O.orderedQty end ) as qtyLocation{N}
from
Products P
join Orders O on P.productId = O.productId
group by P.productId
May 9, 2008 at 10:54 am
bitbucket (5/9/2008)
I can not get the results in one row as you have shown in a simplified manner.From BOL:
The result sets combined using UNION must all have the same structure.
They must have the same number of columns,
and the corresponding result set columns must have compatible data types
Hopefully this will get you started
SELECT Orders.ProductId, Products.ProductName, Orders.OrderQty, Location.LocationName
FROM Orders INNER JOIN
Location ON Orders.LocationId = Location.LocationId INNER JOIN
Products ON Orders.ProductId = Products.Productid
which gives:
ProductIdProductName OrderQty LocationName
1000 xxxxxx 10 Loc1
1000 xxxxxx 0 loc2
1000 xxxxxx 20 loc3
1001 yyyy 15 Loc1
1001 yyyy 45 loc2
1001 yyyy 5 loc3
9999 nnnnn 0 Loc1
9999 nnnnn 90 loc2
9999 nnnnn 50 loc3
Starting with Bitbucket's query - you can use PIVOT to "flip things around", as such:
--=====TEST DATA SETUP
--drop table location
--drop table products
--drop table orders
--=====Create the tables
create table location (locationID int, locationname varchar(30))
create table products(productID int, productname varchar(20))
create table orders (orderid int identity(1,1),productid int, locationid int, orderedqty int)
--======add some data
insert location(locationid, locationname)
select 1,'location1' UNION ALL
select 2,'location2' UNION ALL
select 3,'location3' UNION ALL
select 4,'location4' UNION ALL
select 5,'location5'
insert products (productid,productname)
select 1001,'xxxx' UNION ALL
select 1002,'yyyy' UNION ALL
select 1003,'zzzz' UNION ALL
select 1000,'oooo' UNION ALL
select 9999,'eeee'
insert orders(productid,locationid,orderedqty)
select 1000,1,10 union all
select 1000,2,0 union all
select 1000,3,20 union all
select 1001,1,15 union all
select 1001,2,45 union all
select 1001,3,5 union all
select 9999,1,0 union all
select 9999,2,90 union all
select 9999,3,50
--===get the results
SELECT ProductId, ProductName, [location1],[location2],[location3]
FROM (select Orders.ProductId, Products.ProductName,locationname,orderedqty
from orders INNER JOIN
Location ON Orders.LocationId = Location.LocationId INNER JOIN
Products ON Orders.ProductId = Products.Productid) l
PIVOT (sum(orderedqty) FOR locationname in ([location1],[location2],[location3])) pvt
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 6:06 pm
jprakasam (5/9/2008)
Himy location table has N number of records.
Do you mean N number of Locations?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2008 at 11:52 pm
Yes I have N number of locations in my table. and locations may be added in future.
May 12, 2008 at 6:58 am
Is there any reason that this has to be done in SQL? Most of the time, pivoting data works better in various front-end applications. Reporting Services, Excel, Access, Crystal, all will pivot your data much more efficiently than SQL Server will.
(Leave the data crunching and storage in the database, and let the presentation layer make it look pretty for human eyes.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply