[LINQ] Pivot

在 Line 群組上看見有人發問，基本上就是 TSQL Pivot 轉置就可以完成的需求，故意用 LINQ 來練習看看

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Leave> Data = Leave.GetData();

            var result = Data.GroupBy(g => g.Employee)
                .Select(g => new
                {
                    Employee = g.Key,
                    Personal = g.Where(w => w.Kind == "事假").Sum(s => s.Hours),
                    Sick = g.Where(w => w.Kind == "事假").Sum(s => s.Hours),
                    Company = g.Where(w => w.Kind == "公假").Sum(s => s.Hours),
                    Paternity = g.Where(w => w.Kind == "陪產假").Sum(s => s.Hours)
                });

            Console.WriteLine(" 員工 - 事假 - 病假 - 公假 - 陪產假");
            foreach (var item in result)
            {
                Console.WriteLine($"{item.Employee} - {item.Personal.ToString("00.0")} - {item.Sick.ToString("00.0")} - {item.Company.ToString("00.0")} - {item.Paternity.ToString("00.0")}");
            }
        }
    }

    public class Leave
    {
        public string Employee { get; set; }
        public DateTime Date { get; set; }
        public string Kind { get; set; }
        public decimal Hours { get; set; }

        public static List<Leave> GetData()
        {
            return new List<Leave>()
            {
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,1) , Kind = "事假" , Hours = 3.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,2) , Kind = "事假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,3) , Kind = "事假" , Hours = 4.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,10) , Kind = "病假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,20) , Kind = "病假" , Hours = 1.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,25) , Kind = "公假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,26) , Kind = "公假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,27) , Kind = "公假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,1) , Kind = "事假" , Hours = 6.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,9) , Kind = "病假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,19) , Kind = "病假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,25) , Kind = "陪產假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,26) , Kind = "陪產假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,27) , Kind = "陪產假" , Hours = 8.0m}
            };
        }
    }
}

延伸閱讀
• [SQL] PIVOT 和 UNPIVOT
• [SQL] AdventureWork T-SQL 練習

參考資料
• StackOverFlow
• LINQ Pivot