convert two rows into one row in linq

Question

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

The TSQL that the linq would generate would be something like,

;WITH T1 AS
(
SELECT
        Name,
        Currency1 Currency1Debtor,
        Currency2 Currency2Debtor
    FROM
        (
        SELECT
                    Name,
                    CurrencyName,
                    Debtor
            FROM
                    #Temp
        ) AS SourceTable
    PIVOT
        (
            SUM(Debtor) FOR CurrencyName IN (Currency1, Currency2)
        ) AS PivotTable
),
T2 AS
(
SELECT
            Name ,
            Currency1 Currency1Creditor,
            Currency2 Currency2Creditor
    FROM
            (
            SELECT
                        Name,
                        CurrencyName,
                        creditor
                FROM
                        #Temp
            ) AS SourceTable
    PIVOT
            (
                SUM(creditor) FOR CurrencyName IN (Currency1, Currency2)
            ) AS PivotTable
)
SELECT
            T1.*,
            T2.Currency1Creditor,
            T2.Currency2Creditor
    FROM
            T1
        INNER JOIN
            T2
                ON T1.Name = T2.Name

What would you expect to happen if you *didn't* get exactly "Currency1" followed by "Currency2" in precise pairs? — Jon Skeet, Dec 13 '22 at 12:59
It seems that you are looking for `GroupBy(item => item.name)` — Dmitry Bychenko, Dec 13 '22 at 13:00
OK, but what's the question? Also, please do some proof reading when posting, there are spelling errors. — Gert Arnold, Dec 13 '22 at 13:01
Yeah, that's not a question. We like to help if you're stuck somewhere, so where are you stuck trying this? — Gert Arnold, Dec 13 '22 at 16:13
This question doesn't mention `IQueryable`, or an `MS SQL Server` backend. You specifically asked for a `linq` solution (in multiple places) and now, you have posted your onw answer using `TSQL` — Jodrell, Dec 14 '22 at 12:37
@Jodrell I'm starting to wonder who's question this is. You made substantial changes. The added SQL query can't possibly be generated by any LINQ-based ORM. — Gert Arnold, Dec 14 '22 at 14:09
@GertArnold Totally agree but, the OP did add it as an answer below. On the assumption that the OP was unable to edit their own question, I've added it in the hope the the OP deletes their spurious non-answer answer. — Jodrell, Dec 14 '22 at 14:12

Jodrell · Answer 1 · 2022-12-14T14:04:09.390

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,
        },
    (name, g)  => new
        {
            name,
            currency1Debtor = g.Sum(d => d.currency1Debtor),
            currency1Creditor = g.Sum(d => d.currency1Creditor),
            currency2Debtor = g.Sum(d => d.currency2Debtor),
            currency2Creditor = g.Sum(d => d.currency2Creditor),
        });

`GroupBy` has an `resultSelector` you can use also to avoid the additional `.Select` — Magnus, Dec 14 '22 at 13:58

score 0 · Answer 2 · answered Dec 13 '22 at 13:13

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

https://learn.microsoft.com/en-us/dotnet/csharp/linq/group-query-results And when you have the query you want with your own tables and data: Pseudo code var result = dbContext.Tablename.Query(//whatever you want).Select(x => new(){ Currency1Debitor = (x.currency + x.debitor) }); — Morten Bork, Dec 15 '22 at 12:23

convert two rows into one row in linq

2 Answers2