SQL Server - PIVOT

Question

We are working on a C# application, we've been using Linq to SQL or standard ADO (when performance needed) to work with SQL Server.

We have a table layed out like so:

Customer ID, Year/Month, Product Name, Quantity

Each customer has additional columns per product.

We need display this information in a data grid like so:

Customer, Year/Month, Product A Quantity, Product B Quantity, Product C Quantity, etc.

What query could give us these results? And how could it be dynamic no matter what products are added and removed? We will be using a ListView in WPF for displaying the data.

We would just store the information differently, but they can add/remove products all the time.

Will PIVOT work?

(PS - the product names are really in another table for normalization, I changed it a little for simplicity for you guys)

Answer 1

The sql pivot command can be used but it requires the columns to be hard-coded. You could either hard-code them, use dynamic sql to generate the columns, or only get the raw data from sql without a pivot and do the data massaging in c#.

Answer 2

You can use pivot with dynamic SQL. Following T-SQL code is taken from this article on sqlteam.com. I've tried to modify the sample for your needs. Also beware of dangers using dynamic SQL, it might lead to SQL Injection if a product name contains apostrophe.

Create a stored proc first;

CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

Then try the following (I haven't test it, you might need to add qty to select statement)

EXECUTE crosstab 'select ProductID,CustomerID, YearMonth from sales group by ProductId', 'sum(qty)','ProductId','sales'

Answer 3

If you want to try a method that doesn't involve dynamic SQL, you could go through C#.

This guy ran a test comparing the two: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2005/05/12/5127.aspx