Created
February 12, 2012 03:47
-
-
Save tawman/1806105 to your computer and use it in GitHub Desktop.
PetaPoco returning a PIVOT query as an IEnumerable<dynamic>
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.Collections.Generic; | |
using PetaPoco; | |
namespace PetaPocoPivot.Services | |
{ | |
public class Repository : IRepository | |
{ | |
private readonly Database _database = new Database("AdventureWorks"); | |
public IEnumerable<dynamic> GetEmployeeReport() | |
{ | |
var pivotColumns = GetDepartments(); | |
var pivotQuery = | |
string.Format(@"SELECT * | |
FROM (SELECT EmployeeID, LastName, FirstName, Department, StartDate | |
FROM HumanResources.vEmployeeDepartmentHistory) As EmployeeData | |
PIVOT (MIN(StartDate) FOR Department IN ({0})) as EmployeePivot", pivotColumns); | |
return _database.Query<dynamic>(pivotQuery); | |
} | |
private string GetDepartments() | |
{ | |
var query = Sql.Builder | |
.Select("DISTINCT '[' + Department + ']'") | |
.From("HumanResources.vEmployeeDepartmentHistory") | |
.OrderBy("1"); | |
return string.Join(",", _database.Query<string>(query)); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment