Example using pivot in SQL Server

-- Create a test table
CREATE TABLE [dbo].[AnnualSales]
 (         
       [CustomerID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,         
       [Gender] [char](1) NULL,               
       [City] [varchar](25) NULL,
       [Education] [varchar](25) NULL,               
       [AnnualPurchases] [money] NULL
 ) 
 
 -- Insert some test data
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'University', 6223) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'High School', 4233) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'University', 6560) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','Chicago', 'University', 5001) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','New York', 'University',7034) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Chicago', 'University',5345) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'High School',790) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'None', 240) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','Seattle', 'University', 4300) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'None', 232)

  -- Use Pivot
  -- Columns that get displayed
  SELECT City, 
         M, 
         F
    FROM (
           -- We are summing AnnualPurchases, our pivot defines the columns by gender so the rows are cities
           SELECT Gender, 
                  City, 
                  AnnualPurchases
             FROM annualSales) ansales
            PIVOT (avg(AnnualPurchases) 
              FOR Gender IN (M, F)
          ) 
      AS pvt
ORDER BY City

I wanted to show an example of the Pivot operator in SQL Server. So I implemented an example that I saw in an O'Reilly excel pivot table tutorial. ** page 2 of this tutorial has an error, the average male purchase for New York should be 3566.

Table-Valued Function in SQL Server

---- Create a test table and insert some data 
IF OBJECT_ID('ProductionHistory')>0   
       DROP TABLE ProductionHistory;  

 CREATE TABLE [dbo].[ProductionHistory]
 (         
       [WellID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, 
       [WellName] [varchar](50) NULL,               
       [Date] datetime NULL,         
       [Oil] decimal(18,6) NULL,               
       [Gas] decimal(18,6) NULL,               
       [Water] decimal(18,6) NULL               
 )  

 DECLARE @counter int
 SET @counter = 1            

 WHILE (@counter <=1000)
 BEGIN                           

       INSERT INTO [ProductionHistory](Wellname, [date], [oil], [gas], [water])                     
       VALUES ('Mustang Ranch #1', DATEADD(day, @counter, '2009-01-01'), 50 + (80-50)*RAND(), 10 + (30-10)*RAND(), 1 + (10-1)*RAND())                  

       INSERT INTO [ProductionHistory](Wellname, [date], [oil], [gas], [water])                     
       VALUES ('Mary May #1', DATEADD(day, @counter, '2009-01-01'), 80 + (120-80)*RAND(), 1 + (10-1)*RAND(), 20 + (30-20)*RAND())                  



       SET @counter = @counter + 1
 END  
 GO
---- Create a table valued function to sum values to date
Create FUNCTION dbo.udf_GetCumulativeProduction
 (
       @WellName varchar(50),
       @Date datetime
 )
 RETURNS @CumulativeProductionTable TABLE
 (
       CumulativeOil Decimal(18,6),
       CumulativeGas Decimal(18,6),
       CumulativeWater Decimal(18,6)
 )  

 BEGIN
      
       INSERT INTO @CumulativeProductionTable(CumulativeOil, CumulativeGas, CumulativeWater)
       SELECT
             SUM(Oil), SUM(Gas), Sum(Water)
       FROM
             ProductionHistory
       WHERE WellName = @WellName 
         AND Date <= @Date     
       RETURN
 END
 GO

---- Call the function for each row 
SELECT *
FROM ProductionHistory ph
 CROSS APPLY dbo.udf_GetCumulativeProduction(ph.wellName, ph.date)  

Wanted to have a simple example of a table-valued function. For this example I created some daily production data for a couple of wells. I use the table valued function to calculate the cumulative production to date for each well.

Queryable.Where versus Enumerable.Where

        public ViewResult list1()
        {
            IQueryable<Customer> custs = db.Customers;
            var someCustomers = custs.Where(c => c.lastName.StartsWith("Smith"));
            return View();
        }

        public ViewResult list2()
        {
            IEnumerable<Customer> custs = db.Customers;
            var someCustomers = custs.Where(c => c.lastName.StartsWith("Smith"));
            return View();
        }

I had a mystery for a few minutes the other day when I was testing a snippet of code. I was not getting any records back from list2(), while list1() was functioning like I expected.
 In the test database I was using, all last names were stored in lowercase.

This illustrated the difference between
Queryable.Where (lambda expression converted to an expression tree)
and
 Enumerable.Where. (lambda expression converted to a delegate)

In the first case the filter is executed in the database and is not case sensitive. In the second case, the filter is executed in .NET code using Contains which is case sensitive.