The query result is as follows:

name      CurencyName      Debtor     Creidtor   
agent1     Currency1         20          0
agent1     Currency2         0           10
agent2     Currency1         0           50
agent2     Currency2         0           10

However, I want the result in the following format:

name       currency1Debtor         currency1Creidtor    currency2Debtor         currency2Creidtor 
agent1           20                        0                     0                      10
agent2            0                        50                    0                      10

CodePudding user response：

We really need to know what your schema and table design is that produces these results.

grouping by agentname, might well be what you are looking for, but you likely also need to know more information. For example, having a column value as the group by name, I am not sure there is a syntax for that, which is valid?

You can sort of do what you are asking, if you know in advance precisely how many currencies you have in the system, and debitors and creditors. But you can't make it dynamically, as far as I know.

So basically what you are asking for can't be done. Unless you are fine with a static query, that can account ONLY for the currency, debitor and creditor values you know in advance you want to see.

You can see a related question here: SQL row value as column name

CodePudding user response：

You can do it like this, working here.

var results = d.GroupBy(
    d => d.name,
    d => new
        {
            d.name,
            currency1Debtor = d.CurencyName == "Currency1" ? d.Debtor : 0,
            currency1Creditor = d.CurencyName == "Currency1" ? d.Creditor : 0,
            currency2Debtor = d.CurencyName == "Currency2" ? d.Debtor : 0,
            currency2Creditor = d.CurencyName == "Currency2" ? d.Creditor : 0,
        })
    .Select(g => new
            {
                name = g.Key,
                currency1Debtor = g.Sum(d => d.currency1Debtor),
                currency1Creditor = g.Sum(d => d.currency1Creditor),
                currency2Debtor = g.Sum(d => d.currency2Debtor),
                currency2Creditor = g.Sum(d => d.currency2Creditor),
            });