SQL Server - PIVOT
We are working on a C # application, we are using Linq to SQL or standard ADO (when performance is required) to work with SQL Server.
We have a table structured like this:
Customer ID, year / month, product name, quantity
Each customer has additional columns for each product.
We need to display this information in the data grid like so:
Customer, year / month, product A quantity, product B quantity, product C quantity, etc.
What query can give us these results? And how can it be dynamic, no matter what products are added and removed? We will be using ListView in WPF to display data.
We'll just store information differently, but they can add / remove products all the time.
Will PIVOT work?
(PS - the product names are indeed in a different table for normalization, I modified this a bit for simplicity for you guys)
You can use pivot with dynamic SQL. The following T-SQL code is taken from this article at sqlteam.com . I tried to modify the sample for your needs. Also, beware of the dangers of using dynamic SQL, which can lead to SQL Injection if the product name contains an apostrophe.
Create a stored procedure 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 tested it, you may need to add qty to select the operator)
EXECUTE crosstab 'select ProductID,CustomerID, YearMonth from sales group by ProductId', 'sum(qty)','ProductId','sales'
The pivot sql command can be used, but this requires the columns to be hard-coded. You can either hardcode them, use dynamic sql to generate columns, or get just raw data from sql without pivot point and mass the data in C #.
If you want to try a method that doesn't include dynamic SQL, you can go through C # .
This guy ran a benchmark comparing the two: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2005/05/12/5127.aspx