How To Query Many-to-Many Table (one table's values becomes column headers)
- by CRice
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
