I'm not sure if this is possible from with a SQL query, but I'll give it a go.
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);
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
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!
try this:
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
OUTPUT:
RowID Value1 Value2 CombinedValue
----------- ------ ------ ------------------
1 aaaaa A wood
2 bbbbb B rock, steel, wood
3 ccccc C paper, plastic