Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get the Pivot table to report what I want

Author  Topic 

SergioM
Posting Yak Master

170 Posts
Posted - 2014-10-06 : 14:14:46
I'm so close, but not able to wrap my mind around the last mile. I have a table that stores inventory across different warehouses. My current Pivot table is able to report on the warehouses themselves, but not the stock IN the warehouse! This is what I have so far.

I tried changing this:
	sum(WarehouseID)
	FOR WarehouseID IN ([44],[51],[63],[64],[73],[74],[76],[77],[78])

To this:
	sum(PhysicalInventoryQty)
	FOR WarehouseID IN ([44],[51],[63],[64],[73],[74],[76],[77],[78])


Which is the last step, but SQL will not allow it.


Current Result
ProductID	Physical	NYC	Dropship	FBA	FBA CA	FBA EU	Hackensack	In - Transit	Quantum Certified	Returns	44
03-KX65-ZSM4	NULL	0	0	0	0	0	0	0	0	0	NULL
03-Q96D-ZVTD	NULL	0	0	0	0	0	0	0	0	0	NULL
04-3X1R-013E	0	44	73	51	0	74	64	0	0	63	44


Pivot Query
SELECT 
	ProductID
	,PhysicalInventoryQty AS 'Physical'
	,ISNULL([44],0)		AS 'NYC'
	,ISNULL([73],0)		AS 'Dropship'
	,ISNULL([51],0)		AS 'FBA'
	,ISNULL([77],0)		AS 'FBA CA'
	,ISNULL([74],0)		AS 'FBA EU'
	,ISNULL([64],0)		AS 'Hackensack'
	,ISNULL([76],0)		AS 'In - Transit'
	,ISNULL([78],0)		AS 'Certified'
	,ISNULL([63],0)		AS 'Returns'
	,PVT.[44]
FROM (
	SELECT TOP 10
		[ProductID]
		,[WarehouseID]
		,[PhysicalInventoryQty]
	FROM #warehouses
		) AS p
PIVOT
	(
	sum(WarehouseID)
	FOR WarehouseID IN ([44],[51],[63],[64],[73],[74],[76],[77],[78])
	) AS PVT


Raw SQL
USE [tmp]
GO
/****** Object:  Table #warehouses    Script Date: 10/06/2014 14:08:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #warehouses(
	[ProductID] [nvarchar](50) NOT NULL,
	[WarehouseID] [int] NOT NULL,
	[PhysicalInventoryQty] [int] NULL
) ON [PRIMARY]
GO
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'03-KX65-ZSM4', 16, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'03-KX65-ZSM4', 24, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'03-KX65-ZSM4', 25, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'03-Q96D-ZVTD', 16, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-3X1R-013E', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-3X1R-013E', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-3X1R-013E', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-3X1R-013E', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-3X1R-013E', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-3X1R-013E', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-3X1R-013E', 76, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-4JW2-0AW1', 16, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-4JW2-0AW1', 24, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-4JW2-0AW1', 25, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04624', 55, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'047144054308', 18, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'048107083571', 18, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-QSFT-03LI', 16, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-QSFT-03LI', 24, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'04-QSFT-03LI', 25, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'052963786743', 11, NULL)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205931', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205931', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205931', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205931', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205931', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205931', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205948', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205948', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205948', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205948', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205948', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722205948', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206051', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206051', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206051', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206051', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206051', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206051', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206235', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206235', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206235', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206235', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206235', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206235', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206273', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206273', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206273', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206273', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206273', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206273', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206334', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206334', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206334', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206334', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206334', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206334', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206341', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206341', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206341', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206341', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206341', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206341', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206457', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206457', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206457', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206457', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206457', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206457', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206761', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206761', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206761', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206761', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206761', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206761', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206778', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206778', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206778', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206778', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206778', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206778', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206808', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206808', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206808', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206808', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206808', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206808', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206846', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206846', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206846', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206846', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206846', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206846', 74, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206921_', 44, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206921_', 51, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206921_', 63, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206921_', 64, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206921_', 73, 0)
INSERT #warehouses ([ProductID], [WarehouseID], [PhysicalInventoryQty]) VALUES (N'0609722206921_', 74, 0)



-Sergio
I use Microsoft SQL 2008

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-10-06 : 15:02:36
I don't have 2008, but your query ran fine for me in 2014
Go to Top of Page

SergioM
Posting Yak Master

170 Posts
Posted - 2014-10-06 : 15:39:07
quote:
Originally posted by gbritton

I don't have 2008, but your query ran fine for me in 2014

It does work, but it doesn't do what I want. I would like to know how much stock I have per warehouse. This pivot table basically gives me the warehouse number if stock exits - which is pretty nonsensical. All of them should be showing '0' since there isn't any stock in any of those warehouses.

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-10-06 : 16:40:17
OK -- I ran:


SELECT 
	ProductID
	--,PhysicalInventoryQty AS 'Physical'
	,ISNULL([44],0)		AS 'NYC'
	,ISNULL([73],0)		AS 'Dropship'
	,ISNULL([51],0)		AS 'FBA'
	,ISNULL([77],0)		AS 'FBA CA'
	,ISNULL([74],0)		AS 'FBA EU'
	,ISNULL([64],0)		AS 'Hackensack'
	,ISNULL([76],0)		AS 'In - Transit'
	,ISNULL([78],0)		AS 'Certified'
	,ISNULL([63],0)		AS 'Returns'
	,PVT.[44]
FROM (
	SELECT TOP 10
		[ProductID]
		,[WarehouseID]
		,[PhysicalInventoryQty]
	FROM #warehouses
		) AS p
PIVOT
	(
	sum(PhysicalInventoryQty)
	FOR WarehouseID IN ([44],[51],[63],[64],[73],[74],[76],[77],[78])
	) AS PVT


without errors
Go to Top of Page

SergioM
Posting Yak Master

170 Posts
Posted - 2014-10-06 : 17:50:29
Wow... It was on the tip of my tongue. Thanks for the help!

-Sergio
I use Microsoft SQL 2008
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site