如何查询多对多表(一个表的值成为列标题) [英] How To Query Many-to-Many Table (one table's values becomes column headers)

问题描述

鉴于此表结构，我想弄平多对多关系，并将一个表的名称"字段中的值转换为列标题，并将同一表中的数量转换为列值.当前可行的想法是将值放入Dictionary(哈希表)中并以代码表示此数据，但是我想知道是否存在SQL方式来做到这一点.我还将Linq-to-SQL用于数据访问，因此Linq-to-SQL解决方案将是理想的选择.

[TableA](整数ID)

[TableB](整数ID，字符串名称)

[TableAB](int tableAId，int tableBId，int数量)

fk:TableA.Id加入TableAB.tableAId

fk:TableB.Id加入TableAB.tableBId

有没有一种方法可以查询三个表并返回一个结果，例如:

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

解决方案

对于TSQL解决方案，请使用 167304 .. >

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

解决方案

For a TSQL solution use PIVOT.

For a LINQ solution see 167304.

这篇关于如何查询多对多表(一个表的值成为列标题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！