Pivot with LINQ

Problem

I have SQL table with full of data and I want to present those data in my web page. The problem is SQL table & expected presentation structure was different.

SQL table:

The way I want to present:

Solution

So I have two options, pivot table with SQL query or with LINQ.

Pivot with SQL query :

select * from Package_Test

pivot (max(cost) for Staytype

in ([Home Stays],[Standard],[1st Class],[Superior],[Deluxe],[Boutique])) as packageStaytypeCost

Query result:

Pivot with LINQ:

var aa = DBContext.Package_Test

.GroupBy(a => a.BaseContent_Title)

.Select(g => new

{

Name = g.Key,

HomeStays = g.Where(c => c.StayType.Equals("Home Stays")).Select(c => c.Cost),

Standard = g.Where(c => c.StayType.Equals("Standard")).Select(c => c.Cost),

FirstClass = g.Where(c => c.StayType.Equals("1st Class")).Select(c => c.Cost),

Superior = g.Where(c => c.StayType.Equals("Superior")).Select(c => c.Cost),

Deluxe = g.Where(c => c.StayType.Equals("Deluxe")).Select(c => c.Cost),

Boutique = g.Where(c => c.StayType.Equals("Boutique")).Select(c => c.Cost),

}

).ToList();

LINQ result:

If you want to try this example, use following scripts to create table & insert data set

CREATE TABLE [dbo].[Package_Test](

[BaseContent_Title] [nvarchar](150) NULL,

[StayType] [nvarchar](50) NULL,

[Cost] [int] NULL

) ON [PRIMARY]

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'One Night on Half Board Per person and Twin Sharing', N'Home Stays', 200)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'One Night on Half Board Per person and Twin Sharing', N'Standard', 70)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'One Night on Half Board Per person and Twin Sharing', N'1st Class', 110)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'One Night on Half Board Per person and Twin Sharing', N'Superior', 90)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'One Night on Half Board Per person and Twin Sharing', N'Deluxe', 200)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'One Night on Half Board Per person and Twin Sharing', N'Boutique', 400)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'Two Nights on Half Board Per person and Twin Sharing', N'Home Stays', 200)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'Two Nights on Half Board Per person and Twin Sharing', N'Standard', 100)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'Two Nights on Half Board Per person and Twin Sharing', N'1st Class', 150)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'Two Nights on Half Board Per person and Twin Sharing', N'Superior', 225)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'Two Nights on Half Board Per person and Twin Sharing', N'Deluxe', 300)

INSERT [dbo].[Package_Test] ([BaseContent_Title], [StayType], [Cost]) VALUES (N'Two Nights on Half Board Per person and Twin Sharing', N'Boutique', 400)