How To Query Many-to-Many Table (one table's values becomes column headers)
Posted
by CRice
on Stack Overflow
See other posts from Stack Overflow
or by CRice
Published on 2010-06-07T04:34:17Z
Indexed on
2010/06/07
4:42 UTC
Read the original article
Hit count: 225
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
© Stack Overflow or respective owner