联接的SQL查询中一个字段的多个结果 [英] Multiple results for one field in a joined SQL query
问题描述
我不确定是否可以通过SQL查询来做到这一点，但我会尝试一下.
I'm not sure if this is possible from with a SQL query, but I'll give it a go.
我正在用C#开发一个SharePoint Web部件，该部件连接到SQL数据库并运行查询，然后将结果集数据绑定到gridview.一切正常，但我有一个小障碍.在大多数情况下，我的查询将为每个字段恰好返回一个结果.我正在垂直显示信息，所以看起来大致像这样:
I'm developing a SharePoint web part in C# that connects to a SQL database and runs a query, then databinds that result set to a gridview. It's working fine, but I have a small snag. For the most part, my query will return exactly one result for every field. I am displaying the information vertically, so it looks roughly like this:
Customer Name Mr. Customer
Customer Address 980 Whatever St.
Customer City Tallahassee
一切正常.但是，数据库中的表之一几乎总是会返回多个结果.它列出了用于不同产品的不同类型的物料，因此架构也有所不同，因为虽然每个客户显然拥有一个姓名，一个地址，一个城市等，但他们都将至少拥有一个产品，并且该产品将具有至少一种材料.如果我要单独显示这些信息，它将看起来像这样:
Everything displays fine. However, one of the tables in the database will pretty much always return multiple results. It lists different types of materials used for different products, so the schema is different because while each customer will obviously have one name, one address, one city, etc., they will all have at least one product, and that product will have at least one material. If I were to display that information on its own, it would look something like this:
Product Steel Type Wood Type Paper Type
-----------------------------------------------------------------------------
Widget Thick Oak Bond
Whatsit Thin Birch
Thingamabob Oak Cardstock
理想情况下，我想最终结果应该是这样的:
Ideally, I suppose the end result would be something like:
Customer Name Mr. Customer
Customer Address 980 Whatever St.
Customer City Tallahassee
Widget Steel Thick
Widget Wood Oak
Widget Paper Bond
Whatsit Steel Thin
Whatsit Wood Birch
Thingamabob Wood Oak
Thingamabob Paper Cardstock
另一个可接受的结果可能类似于以下内容，添加了几列，但只返回了这些字段的多个结果:
Another acceptable result could be something like the following, adding a few columns but only returning multiple results for those fields:
Customer Name Mr. Customer
Customer Address 980 Whatever St.
Customer City Tallahassee
Product Steel Type Wood Type Paper Type
Widget Thick Oak Bond
Whatsit Thin Birch
Thingamabob Oak
我愿意接受任何建议.如果可能的话，我想在没有单独查询的情况下将其包括在结果集中.这是我用来提取数据的代码:
I'm open to any suggestions. I'd like to include this in the result set without a separate query, if that's possible. Here is the code that I am using to pull in the data:
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Specs");
DataSet flipped_ds = FlipDataSet(ds);
DataView dv = flipped_ds.Tables[0].DefaultView;
GridView outputGrid = new GridView();
outputGrid.ShowHeader = false;
outputGrid.DataSource = dv;
outputGrid.DataBind();
Controls.Add(outputGrid);
我会列出我的SQL查询，但这是巨大的.我现在要输入一百多个字段，其中包含许多SUBSTRING格式和连接，所以这会浪费空间，但这实际上是一个非常简单的查询，我在其中使用AS语句选择字段以获取所需的名称，并使用一些LEFT JOIN插入我需要的数据，而无需进行几次查询.产品/物料表的架构如下所示:
I would list my SQL query, but it's enormous. I'm pulling in well over one hundred fields right now, with lots of SUBSTRING formatting and concatenation, so it would be a waste of space, but it's really a fairly simple query where I'm selecting fields with the AS statement to get the names that I want, and using a few LEFT JOINs to pull in the data I need without several queries. The schema of the product/material table is something like this:
fldMachineID
fldProductType
fldSteel
fldWood
fldPaper
很显然，每个客户都有很多条目，每个不同的fldProductType值一个.如果我遗漏了任何内容，则可以添加.感谢大家的时间和帮助！
So obviously, each customer has a number of entries, one for each different fldProductType value. If I'm leaving anything out, I can add it. Thanks for everyone's time and help!
推荐答案
尝试一下:
DECLARE @TableA table (RowID int, Value1 varchar(5), Value2 varchar(5))
DECLARE @TableB table (RowID int, TypeOf varchar(10))
INSERT INTO @TableA VALUES (1,'aaaaa','A')
INSERT INTO @TableA VALUES (2,'bbbbb','B')
INSERT INTO @TableA VALUES (3,'ccccc','C')
INSERT INTO @TableB VALUES (1,'wood')
INSERT INTO @TableB VALUES (2,'wood')
INSERT INTO @TableB VALUES (2,'steel')
INSERT INTO @TableB VALUES (2,'rock')
INSERT INTO @TableB VALUES (3,'plastic')
INSERT INTO @TableB VALUES (3,'paper')
;WITH Combined AS
(
SELECT
a.RowID,a.Value1,a.Value2,b.TypeOf
FROM @TableA a
LEFT OUTER JOIN @TableB b ON a.RowID=b.RowID
)
SELECT
a.*,dt.CombinedValue
FROM @TableA a
LEFT OUTER JOIN (SELECT
c1.RowID
,STUFF(
(SELECT
', ' + TypeOf
FROM Combined c2
WHERE c2.rowid=c1.rowid
ORDER BY c1.RowID, TypeOf
FOR XML PATH('')
)
,1,2, ''
) AS CombinedValue
FROM Combined c1
GROUP BY RowID
) dt ON a.RowID=dt.RowID
输出:
RowID Value1 Value2 CombinedValue
----------- ------ ------ ------------------
1 aaaaa A wood
2 bbbbb B rock, steel, wood
3 ccccc C paper, plastic
这篇关于联接的SQL查询中一个字段的多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
