DataGrid Binding Question - Multiple Rows as One.

[Mike]

Hi!
Been stuck on this one for a bit. Would really appreciate any help on
this one.

In Regards to ASP.NET

To start. I have a sql database table with the following data and
design (ex)

------------------------------------
provider_id | plan_id | plan cost
------------------------------------
1 US-1 20.00
1 US-2 30.00
1 US-3 40.00
2 UK-1 10.00
2 UK-2 20.00
2 UK-3 32.00

I need to display this in a way (DataGrid, DataList, etc...)
where I can view all provider items on the same, line and select a
plan
using a radiobuttonlist or similar.

I'm not sure if it is something I need to do in the database query or
something
at the application level.

It will need to look something like this:

------------------------------------------
provider_id | plan_1 | plan_2 | plan 3
------------------------------------------
1 20.00 30.00 40.00
2 10.00 20.00 32.00

I would like the to be able to select a dollar amount(by radiobutton
if possible) and have the plan_id as the value if possible.

Has anybody got something like this working.

 

[q]

From SQL Books online

Cross-Tab Reports
Sometimes it is necessary to rotate results so that columns are
presented horizontally and rows are presented vertically. This is known
as creating a PivotTable®, creating a cross-tab report, or rotating data.

Assume there is a table Pivot that has one row per quarter. A SELECT of
Pivot reports the quarters vertically:

Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

A report must be produced with a table that contains one row for each
year, with the values for each quarter appearing in a separate column,
such as:

Year Q1 Q2 Q3 Q4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4


These are the statements used to create the Pivot table and populate it
with the data from the first table:

USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

This is the SELECT statement used to create the rotated results:

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

This SELECT statement also handles a table in which there are multiple
rows for each quarter. The GROUP BY combines all rows in Pivot for a
given year into a single row in the output. When the grouping operation
is being performed, the CASE functions in the SUM aggregates are applied
in such a way that the Amount values for each quarter are added into the
proper column in the result set and 0 is added to the result set columns
for the other quarters.

If the results of this SELECT statement are used as input to a
spreadsheet, it is easy for the spreadsheet to calculate a total for
each year. When the SELECT is used from an application it may be easier
to enhance the SELECT statement to calculate the yearly total. For example:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO

Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort
of information as shown in the example, but in a slightly different format.