Create Table TestSort
(ID  int identity(1,1),
Name nvarchar(30),
Color nvarchar(15)
)

Insert into TestSort (Name,color)
Values  ('Adjustable Race',null)
       ,('Bearing Ball',null)
       ,('Headset Ball Bearings',null)
       ,('LL Crankarm','Black')
       ,('ML Crankarm','Black')
       ,('Chainring','Black')
       ,('Front Derailleur Cage','Silver')
       ,('Front Derailleur Linkage','Silver')
       ,('Lock Ring','Silver')
       ,('HL Road Frame - Red, 62','Red')
       ,('HL Road Frame - Red, 48','Red')
       ,('LL Road Frame - Red, 44','Red')
       ,('Full-Finger Gloves, M','RED')
       ,('Road-550-W Yellow, 38','Yellow')
       ,('Road-550-W Yellow, 40','Yellow')
       ,('Road-550-W Yellow, 42','Yellow')
       ,('Classic Vest, S','Blue')
       ,('Classic Vest, M','Blue')
       ,('Classic Vest, L','Blue')

Select t.ID,t.Name,t.Color from TestSort as t order by t.Color

Select t.ID,t.Name,t.Color from TestSort as t 
Where t.color is not null
order by  Case t.Color
When 'Red' Then Null
Else t.color 
End;

Select * into Simulation from 
(Select t.ID,t.Name,t.Color,
Case t.Color
When 'Red' Then Null
Else t.color 
End RedNull
from TestSort as t 
Where t.color is not null) A

Select * from Simulation order by Rednull

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
[Product].[Product Categories].[Category] on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
[Measures].[Internet Sales Amount] > 0
) on rows
From [Adventure Works]

با توجه به شرطی که اعمال شده است، فقط دسته بندی محصولاتی در خروجی می  آید که فروش اینترنتی آنها بیشتر از صفر  یا برابر Null نباشند.

استفاده از کلید واژه ی  Having  در هر محور کاری شبیه به انجام عمل فیلترینگ می‌ باشد .

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
[Product].[Product Categories].[Category]
having [Measures].[Internet Sales Amount] > 0 on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
[Measures].[Internet Sales Amount] > 500000
  ) on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
[Measures].[Internet Sales Amount] > 500000
and
[Measures].[Internet Sales Amount] < 750000
) on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
[Measures].[Internet Sales Amount] > 750000
or
[Measures].[Internet Sales Amount] < 500000
  ) on rows
From [Adventure Works]

در زیر با استفاده از And، شرط برروی میزان فروش نمایندگان فروش نیز قرارداده شده است.

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
(
[Measures].[Internet Sales Amount] > 750000
or
[Measures].[Internet Sales Amount] < 500000
)
and
[Measures].[Reseller Sales Amount] < 15000000
) on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
[Measures].[Internet Sales Amount]
>
[Measures].[Reseller Sales Amount]
) on rows
From [Adventure Works]

ایجاد فیلترینگ با استفاده از currentmember  و عملگر  Is .

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
[Product].[Product Categories].currentmember
is
[Product].[Product Categories].[Category].[Bikes]
) on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
[Product].[Product Categories].currentmember
is
[Product].[Product Categories].[Category].[Bikes]
or
[Product].[Product Categories].currentmember
is
[Product].[Product Categories].[Category].[Accessories]
) on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
(
[Product].[Product Categories].currentmember
is
[Product].[Product Categories].[Category].[Bikes]
and
[Measures].[Reseller Sales Amount] > 1000000
)
or
(
[Product].[Product Categories].currentmember
is
[Product].[Product Categories].[Category].[Accessories]
)
and
[Measures].[Reseller Sales Amount] > 750000
)on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
filter(
[Product].[Product Categories].[Category],
not
(
[Product].[Product Categories].currentmember
is
[Product].[Product Categories].[Category].[Clothing]
)
) on rows
From [Adventure Works]

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
[Product].[Product Categories].[Subcategory] on rows
From [Adventure Works]

با استفاده از تابع Non Empty، ردیف ‌ها یا ستون هایی حذف می‌ گردند که تمامی مقادیر آن‌ها ، در آن ردیف یا در آن ستون برابر  Null  باشند.

Select
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} on columns,
non empty [Product].[Product Categories].[Subcategory] on rows
From [Adventure Works]

 <connectionStrings>
   <add name="dmEntities" connectionString="metadata=res://*/Models.dmDataModel.csdl|res://*/Models.dmDataModel.ssdl|res://*/Models.dmDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=(local);Initial Catalog=dm;Integrated Security=True;Application Name=My Application Name;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
 </connectionStrings>

protected void Application_Start(object sender, EventArgs e)
{
  HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
}

using System.Collections.Generic;
namespace CustomerOrdersSample.Domain
{
   public class Customer
   {
       public virtual int Id { get; set; }
       public virtual string Name { get; set; }
       public virtual IList<Order> Orders { get; set; }
   }
}

using System;
using System.Collections.Generic;
namespace CustomerOrdersSample.Domain
{
   public class Order
   {
       public virtual int Id { get; set; }
       public virtual DateTime OrderDate { set; get; }
       public virtual Customer Customer { get; set; }
       public virtual IList<OrderItem> OrderItems { set; get; }
   }
}

namespace CustomerOrdersSample.Domain
{
   public class OrderItem
   {
       public virtual int Id { get; set; }
       public virtual Product Product { get; set; }
       public virtual int Quntity { get; set; }
       public virtual Order Order { set; get; }
   }
}

namespace CustomerOrdersSample.Domain
{
   public class Product
   {
       public virtual int Id { set; get; }
       public virtual string Name { get; set; }
       public virtual decimal UnitPrice { get; set; }
   }
}

    create table Customers (
      CustomerId INT IDENTITY NOT NULL,
      Name NVARCHAR(255) null,
      primary key (CustomerId)
   )

   create table Orders (
      OrderId INT IDENTITY NOT NULL,
      OrderDate DATETIME null,
      CustomerId INT null,
      primary key (OrderId)
   )

   create table OrderItems (
      OrderItemId INT IDENTITY NOT NULL,
      Quntity INT null,
      ProductId INT null,
      OrderId INT null,
      primary key (OrderItemId)
   )

   create table Products (
      ProductId INT IDENTITY NOT NULL,
      Name NVARCHAR(255) null,
      UnitPrice NUMERIC(19,5) null,
      primary key (ProductId)
   )

   alter table Orders
       add constraint fk_Customer_Order
       foreign key (CustomerId)
       references Customers

   alter table OrderItems
       add constraint fk_Product_OrderItem
       foreign key (ProductId)
       references Products

   alter table OrderItems
       add constraint fk_Order_OrderItem
       foreign key (OrderId)
       references Orders

SET IDENTITY_INSERT [Customers] ON;
GO
INSERT INTO [Customers] ([CustomerId],[Name]) VALUES (1,N'Customer1');
GO
SET IDENTITY_INSERT [Customers] OFF;
GO
SET IDENTITY_INSERT [Products] ON;
GO
INSERT INTO [Products] ([ProductId],[Name],[UnitPrice]) VALUES (1,N'Product1',1000.00000);
GO
INSERT INTO [Products] ([ProductId],[Name],[UnitPrice]) VALUES (2,N'Product2',2000.00000);
GO
INSERT INTO [Products] ([ProductId],[Name],[UnitPrice]) VALUES (3,N'Product3',3000.00000);
GO
SET IDENTITY_INSERT [Products] OFF;
GO
SET IDENTITY_INSERT [Orders] ON;
GO
INSERT INTO [Orders] ([OrderId],[OrderDate],[CustomerId]) VALUES (1,{ts '2011-01-07 11:25:20.000'},1);
GO
SET IDENTITY_INSERT [Orders] OFF;
GO
SET IDENTITY_INSERT [OrderItems] ON;
GO
INSERT INTO [OrderItems] ([OrderItemId],[Quntity],[ProductId],[OrderId]) VALUES (1,10,1,1);
GO
INSERT INTO [OrderItems] ([OrderItemId],[Quntity],[ProductId],[OrderId]) VALUES (2,5,2,1);
GO
INSERT INTO [OrderItems] ([OrderItemId],[Quntity],[ProductId],[OrderId]) VALUES (3,20,3,1);
GO
SET IDENTITY_INSERT [OrderItems] OFF;
GO

var list = session.QueryOver<Customer>().List();

foreach (var customer in list)
{
      foreach (var order in customer.Orders)
      {
          foreach (var orderItem in order.OrderItems)
          {
                Console.WriteLine("{0}:{1}:{2}", customer.Name, order.OrderDate, orderItem.Product.Name);
           }
       }
}

Customer1:2011/01/07 11:25:20 :Product1
Customer1:2011/01/07 11:25:20 :Product2
Customer1:2011/01/07 11:25:20 :Product3

var list = session
                   .QueryOver<Customer>()
                   .Fetch(c => c.Orders).Eager
                   .Fetch(c => c.Orders.First().OrderItems).Eager
                   .Fetch(c => c.Orders.First().OrderItems.First().Product).Eager
                   .List();

foreach (var customer in list)
{
     foreach (var order in customer.Orders)
     {
           foreach (var orderItem in order.OrderItems)
           {
               Console.WriteLine("{0}:{1}:{2}", customer.Name, order.OrderDate, orderItem.Product.Name);
            }
      }
}

...
.TransformUsing(NHibernate.Transform.Transformers.DistinctRootEntity)
.List();

var list = session
                   .Query()
                   .FetchMany(c => c.Orders)
                   .ThenFetchMany(o => o.OrderItems)
                   .ThenFetch(p => p.Product)
                   .ToList();

using System;

namespace Pivot.Sample1
{
    public class Expense
    {
        public DateTime Date { set; get; }
        public string Department { set; get; }
        public decimal Expenses { set; get; }
    }
}

using System;
using System.Collections.Generic;

namespace Pivot.Sample1
{
    public class ExpenseDataSource
    {
        public static IList<Expense> ExpensesDataSource()
        {
            return new List<Expense>
            {
                new Expense { Date = new DateTime(2011,11,1), Department = "Computer", Expenses = 100 },
                new Expense { Date = new DateTime(2011,11,1), Department = "Math", Expenses = 200 },
                new Expense { Date = new DateTime(2011,11,1), Department = "Physics", Expenses = 150 },

                new Expense { Date = new DateTime(2011,10,1), Department = "Computer", Expenses = 75 },
                new Expense { Date = new DateTime(2011,10,1), Department = "Math", Expenses = 150 },
                new Expense { Date = new DateTime(2011,10,1), Department = "Physics", Expenses = 130 },

                new Expense { Date = new DateTime(2011,9,1), Department = "Computer", Expenses = 90 },
                new Expense { Date = new DateTime(2011,9,1), Department = "Math", Expenses = 95 },
                new Expense { Date = new DateTime(2011,9,1), Department = "Physics", Expenses = 100 }
            };
        }
    }
}

using System.Collections;
using System.Linq;

namespace Pivot.Sample1
{
    public class PivotTable
    {
        public static IList ExpensesCrossTab()
        {
            return ExpenseDataSource
                        .ExpensesDataSource()
                        .GroupBy(t =>
                                   new
                                   {
                                       Year = t.Date.Year,
                                       Month = t.Date.Month
                                   })
                        .Select(myGroup =>
                                   new
                                   {
                                       //Year = myGroup.Key.Year,
                                       Month = myGroup.Key.Month,
                                       ComputerDepartment = myGroup.Where(x => x.Department == "Computer").Sum(x => x.Expenses),
                                       MathDepartment = myGroup.Where(x => x.Department == "Math").Sum(x => x.Expenses),
                                       PhysicsDepartment = myGroup.Where(x => x.Department == "Physics").Sum(x => x.Expenses)
                                   })
                        .ToList();
        }
    }
}

using System;

namespace Pivot.Sample2
{
    public class StudentStat
    {
        public int Id { set; get; }
        public string Name { set; get; }
        public DateTime Date { set; get; }
        public bool IsPresent { set; get; }
    }
}

using System;
using System.Collections.Generic;

namespace Pivot.Sample2
{
    public class StudentsStatDataSource
    {
        public static IList<StudentStat> CreateMonthlyReportDataSource()
        {
            var result = new List<StudentStat>();
            var rnd = new Random();

            for (int day = 1; day < 6; day++)
            {
                for (int student = 1; student < 6; student++)
                {
                    result.Add(new StudentStat
                    {
                        Id = student,
                        Date = new DateTime(2011, 11, day),
                        IsPresent = rnd.Next(-1, 1) == 0 ? true : false,
                        Name = "student " + student
                    });
                }
            }

            return result;
        }
    }
}

using System.Collections;
using System.Linq;

namespace Pivot.Sample2
{
    public class PivotTable
    {
        public static IList StudentsStatCrossTab()
        {
            return StudentsStatDataSource
                        .CreateWeeklyReportDataSource()
                        .GroupBy(x =>
                                  new
                                  {
                                      x.Id
                                  })
                        .Select(myGroup =>
                                  new
                                  {
                                      myGroup.Key.Id,
                                      Name = myGroup.First().Name,
                                      Day1IsPresent = myGroup.Where(x => x.Date.Day == 1).First().IsPresent,
                                      Day2IsPresent = myGroup.Where(x => x.Date.Day == 2).First().IsPresent,
                                      Day3IsPresent = myGroup.Where(x => x.Date.Day == 3).First().IsPresent,
                                      Day4IsPresent = myGroup.Where(x => x.Date.Day == 4).First().IsPresent,
                                      Day5IsPresent = myGroup.Where(x => x.Date.Day == 5).First().IsPresent,
                                      PresentsCount = myGroup.Where(x => x.IsPresent).Count(),
                                      AbsentsCount = myGroup.Where(x => !x.IsPresent).Count()
                                  })
                        .ToList();
        }
    }
}