Do you want to count products for each transaction or you want to pivot products?
I have create A_TEST database on MS SQL server 2005 Express Edition, which contain 'Transaction' table with some values.
USE [A_TEST];
DECLARE @sqry NVARCHAR(2000)
DECLARE @mqry NVARCHAR(2000)
DECLARE @fqry NVARCHAR(2000)
DECLARE @cols NVARCHAR(2000)
SET @cols = STUFF(( SELECT DISTINCT '],[' + CONVERT(NVARCHAR, [ProductId])
FROM [dbo].[Transactions]
ORDER BY '],[' + CONVERT(NVARCHAR, [ProductId])
FOR XML PATH('')), 1, 2, '') + ']'
SET @sqry = 'SELECT [OrderId], COUNT([OrderId]) AS [CountOfProducts] ' +
'FROM [dbo].[Transactions] ' +
'GROUP BY [OrderId]'
EXECUTE (@sqry)
SET @sqry = 'SELECT [OrderId], [ProductId], COUNT([OrderId]) AS [CountOfProducts] ' +
'FROM [dbo].[Transactions] ' +
'GROUP BY [OrderId], [ProductId]'
EXECUTE (@sqry)
SET @mqry = 'SELECT [OrderId], ' + @cols + ' ' +
'FROM (' + @sqry + ') AS DT ' +
'PIVOT (COUNT(DT.[CountOfProducts]) FOR DT.[ProductId] IN (' + @cols + ')) AS PT ' +
'ORDER BY PT.[OrderId]'
EXECUTE (@mqry)
SET @cols = STUFF((SELECT DISTINCT ']+S.[' + CONVERT(NVARCHAR, [ProductId])
FROM [dbo].[Transactions]
ORDER BY ']+S.[' + CONVERT(NVARCHAR, [ProductId])
FOR XML PATH('')), 1, 2, '') + ']';
SET @fqry = 'SELECT S.*, (' + @cols + ') AS [SumOfProductsInOrder] ' +
'FROM (' + @mqry + ') AS S '
EXECUTE(@fqry)
Here are the results for each
EXECUTE(query)
command:
1) global count of products
OrderID | CountOfProducts
------------------------------
1 | 1
2 | 2
3 | 2
4 | 2
5 | 4
2) count of products for each ProductId
OrderID | ProductId | CountOfProducts
--------------------------------------------------
1 | 1000 | 1
2 | 1000 | 1
3 | 1000 | 1
4 | 1000 | 1
5 | 1000 | 1
2 | 1001 | 1
4 | 1001 | 1
5 | 1001 | 1
3 | 1002 | 1
5 | 1002 | 1
5 | 1003 | 1
3) pivot and count of ProductId for each OrderId
OrderID | 1000 | 1001 | 1002 | 1003
--------------------------------------------------------
1 | 1 | 0 | 0 | 0
2 | 1 | 1 | 0 | 0
3 | 1 | 0 | 1 | 0
4 | 1 | 1 | 0 | 0
5 | 1 | 1 | 1 | 1
4) total
OrderID | 1000 | 1001 | 1002 | 1003 | SumOfProductsInOrder
-------------------------------------------------------------------------
1 | 1 | 0 | 0 | 0 | 1
2 | 1 | 1 | 0 | 0 | 2
3 | 1 | 0 | 1 | 0 | 2
4 | 1 | 1 | 0 | 0 | 2
5 | 1 | 1 | 1 | 1 | 4