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)
source to share
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'
source to share
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
source to share