How To Query Many-to-Many Table (one table's v

Question

Given this table structure, I want to flatten out the many-to-many relationships and make the values in the Name field of one table into column headers and the quantities from the same table into column values. The current idea which will work is to put the values into a Dictionary (hashtable) and represent this data in code but im wondering if there is a SQL way to do this. I am also using Linq-to-SQL for data access so a Linq-to-SQL solution would be ideal.

[TableA] (int Id)

[TableB] (int id, string Name)

[TableAB] (int tableAId, int tableBId, int Quantity)

fk: TableA.Id joins to TableAB.tableAId

fk: TableB.Id joins to TableAB.tableBId

Is there a way I can query the three tables and return one result for example:

TableA
[Id]
1

TableB
[Id], [Name]
1, "Red"
2, "Green"
3, "Blue"

TableAB
[TableAId], [TableBId], [Quantity]
1           1           5
1           2           6
1           3           7

Query Result:
[TableA.Id], [Red], [Green], [Blue]
1,           5,     6,       7

Answer 1

Here's an example LINQ query:

from rows in (
    from a in TableAs
    join ab in TableABs on a.Id equals ab.TableAId
    join b in TableBs on ab.TableBId equals b.Id
    select new {a.Id, b.Name, ab.Quantity}
)
group rows by new {rows.Id} into g
select new {
    AId = g.Key.Id, 
    Red = g.Sum(x => x.Name == "Red" ? x.Quantity : 0),
    Green = g.Sum(x => x.Name == "Green" ? x.Quantity : 0),
    Blue = g.Sum(x => x.Name == "Blue" ? x.Quantity : 0),
}

If you don't know the names of groups in advance, use the subquery:

from a in TableAs
join ab in TableABs on a.Id equals ab.TableAId
join b in TableBs on ab.TableBId equals b.Id
select new {a.Id, b.Name, ab.Quantity}

And generate a new dataset by iterating over that. I don't think Linq allows you to create a result with a dynamic number of columns.

Answer 2

This kind of operation is not supported by the concepts behind the relational database. Using a software package that lets you create a pivot table may help.

Answer 3

For a TSQL solution use PIVOT.

For a LINQ solution see 167304.