How to transpose data in Linq? - Postgresql

Velmurugan
January 28, 2023
325 views
0 votes
2 Answers

I get below data from Postgresql table, need to sum the ListDate field for each month and transpose.
I tried this link Transpose a datatable using linq query , but not feasible or right approach to achieve it.

ListDate	MM	FC	AMS	KS
2023-01-01	12	13	34	26
2023-01-01	22	23	44	46
2022-12-01	32	13	34	26
2023-12-01	42	13	64	16
2023-11-10	62	13	94	36
2023-11-23	02	13	34	46

Expected Result –

.	JAN-23	DEC-22	NOV-22
MM	34	74	64
FC	36	26	26
AMS	78	98	118
KS	72	42	82

Tags: c#linq postgresql

Answers

this is my class

 public class Data
    {
        public string Date { get; set; }
        public int MM { get; set; }
        public int FC { get; set; }
        public int AMS { get; set; }
        public int KS { get; set; }
    }

var dataList=new List<Data>
        {
            new Data
            {
                 AMS=34, Date="2023-01-01", FC=13, KS=26, MM=12
            },  new Data
            {
                 AMS=44, Date="2023-01-01", FC=23, KS=46, MM=22
            },  new Data
            {
                 AMS=34, Date="2022-12-01", FC=13, KS=26, MM=32
            },  new Data
            {
                 AMS=64, Date="2023-12-01", FC=13, KS=16, MM=42
            },  new Data
            {
                 AMS=94, Date="2023-11-10", FC=13, KS=36, MM=62
            },  new Data
            {
                 AMS=34, Date="2023-11-23", FC=13, KS=46, MM=02
            }
        }.ToList();

  var query = dataList
    .GroupBy(g =>
        g.Date.ToString()
    )
    .Select(group => new
    {
        Date= DateTime.Parse(group.Key).ToString("dd MMMM", CultureInfo.InvariantCulture),
        MM = group.Sum(s => s.MM),
        FC = group.Sum(s => s.FC),
        AMS = group.Sum(a => a.AMS),
        KS = group.Sum(c => c.KS)
    });

The first point:

12-01-2023 should be 2022-12-01 in order, the question data to match the Expected Result.

If we have the following list:

    public class item
    {
        public item(string _dt,int _MM,int _FC,int _AMS,int _KS)
        {
            dt = _dt;
            MM = _MM;
            FC = _FC;
            AMS = _AMS;
            KS = _KS;
        }
        public string dt { get;  set; }
        public int MM { get; set; }
        public int FC { get; set; }
        public int AMS { get; set; }
        public int KS { get; set; }
    }


List<item> _list=new List<item>();
_list.Add(new item("2023-01-01", 12, 13, 24, 26));
_list.Add(new item("2023-01-01", 22, 23, 44, 46));
_list.Add(new item("2022-12-01", 32, 13, 24, 26));
_list.Add(new item("2022-12-01", 42, 13, 64, 26));
_list.Add(new item("2023-11-10", 62, 13, 94, 36));
_list.Add(new item("2023-11-23", 02, 13, 34, 46));

We can use Substring(0,7) to group by year and month.
Now the key of group by year-month(ex: 2023-12). We can get the month name using DateTimeFormat.GetMonthName
and calculate the last two digits of the year using the substring(2,2)

And finally, collect the parameters and convert them into a list

      var result = _list
         .GroupBy(g =>
             g.dt.Substring(0,7)
         )

         .Select(group => new
         {
             dateOfMonth = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(int.Parse(group.Key.Split('-')[1]))
              + "-" + group.Key.Split('-')[0].Substring(2,2),
             MM = group.Sum(s => s.MM),
             FC = group.Sum(s => s.FC),
             AMS = group.Sum(a => a.AMS),
             KS = group.Sum(c => c.KS)
         }).ToList();

Result:

Please signup or login to give your own answer.

Click here to cancel reply.

How to transpose data in Linq? – Postgresql

Answers