I have a table ProductStatusTable that contains the following data.  

LOCATION|PRODUCT|SERIALNUMBER|STATUSCODE|
B1------|MIRROR-|M1234-------|0---------
B1------|MIRROR-|M8379-------|1---------
B1------|MIRROR-|M2277-------|3---------
B1------|MIRROR-|M8878-------|3---------
B1------|MIRROR-|M3355-------|4---------
MAIN----|MIRROR-|M7454-------|1---------
MAIN----|MIRROR-|M0023-------|2---------
MAIN----|TABLE--|T3645-------|0---------
MAIN----|TABLE--|T1837-------|4---------
MAIN----|TABLE--|T9432-------|4---------

I have a query that sorts of gets it where I wanted - counting the individual statuses:

SELECT Location, Product, COUNT(StatusCode) As Count
FROM ProductStatusTable
GROUP BY Location, Product, StatusCode
ORDER BY Location, Product, StatusCode

To yield:

LOCATION|PRODUCT|COUNT|
B1------|MIRROR-|----1
B1------|MIRROR-|----1
B1------|MIRROR-|----2
B1------|MIRROR-|----1
MAIN----|MIRROR-|----1
MAIN----|MIRROR-|----1
MAIN----|TABLE--|----1
MAIN----|TABLE--|----2

But, alas, this is not what I really want to do.  I want to do something more complicated, and am not quite sure how to approach it. 

 First, the status codes stand for something:  codes 0 and 1 = IN STOCK, code 2 = BROKEN, code 3 = STOLEN, and code 4 = SOLD.

I want to yield this:

LOCATION|PRODUCT|#IN STOCK|#BROKEN|#STOLEN|#SOLD|
B1------|MIRROR-|--------2|------0|------2|----1|
MAIN----|MIRROR-|--------1|------1|------0|----0|
MAIN----|TABLE--|--------1|------0|------0|----2|

Here is the script to create the data:

USE [Northwind]
GO
/****** Object:  Table [dbo].[ProductStatusTable]    Script Date: 07/03/2012 10:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProductStatusTable](
	[Location] [varchar](10) NULL,
	[Product] [varchar](20) NULL,
	[SerialNumber] [varchar](20) NULL,
	[StatusCode] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'B1', N'MIRROR', N'M1234', 0)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'B1', N'MIRROR', N'M8379', 1)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'B1', N'MIRROR', N'M3355', 4)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'B1', N'MIRROR', N'M2277', 3)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'B1', N'MIRROR', N'M8878', 3)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'MAIN', N'MIRROR', N'M0023', 2)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'MAIN', N'MIRROR', N'M7454', 1)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'MAIN', N'TABLE', N'T1837', 4)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'MAIN', N'TABLE', N'T9432', 4)
INSERT [dbo].[ProductStatusTable] ([Location], [Product], [SerialNumber], [StatusCode]) VALUES (N'MAIN', N'TABLE', N'T3645', 0)

SELECT Location
,Product
,(SELECT COUNT(StatusCode) FROM ProductStatusTable p WHERE (StatusCode = 0 OR StatusCode = 1) AND p.Location = t.Location AND p.Product = t.Product) AS "IN STOCK"
,(SELECT COUNT(StatusCode) FROM ProductStatusTable p WHERE (StatusCode = 2) AND p.Location = t.Location AND p.Product = t.Product) AS "BROKEN"
,(SELECT COUNT(StatusCode) FROM ProductStatusTable p WHERE (StatusCode = 3) AND p.Location = t.Location AND p.Product = t.Product) AS "STOLEN"
,(SELECT COUNT(StatusCode) FROM ProductStatusTable p WHERE (StatusCode = 4) AND p.Location = t.Location AND p.Product = t.Product) AS "SOLD"
FROM ProductStatusTable t
GROUP BY Location, Product
ORDER BY Location, Product

quote:

---

Originally posted by nigelrivett

SELECT Location, Product,
[IN STOCK] = sum(case when StatusCode in (0,1) then 1 else 0 end),
BROKEN = sum(case when StatusCode = 2 then 1 else 0 end),
STOLEN = sum(case when StatusCode = 3 then 1 else 0 end),
SOLD = sum(case when StatusCode = 4 then 1 else 0 end)
FROM ProductStatusTable
GROUP BY Location, Product
ORDER BY Location, Product


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---