DataGrid Binding Question - Multiple Rows as One.

Discussion in 'ASP .Net Datagrid Control' started by Mike, Oct 13, 2004.

  1. Mike

    Mike Guest

    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.
     
    Mike, Oct 13, 2004
    #1
    1. Advertising

  2. Mike

    Guest

    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.



    Mike wrote:

    > 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.
    >
     
    , Oct 14, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SSP
    Replies:
    5
    Views:
    866
  2. Brian
    Replies:
    2
    Views:
    1,925
    Brian
    Dec 24, 2003
  3. Mike
    Replies:
    1
    Views:
    511
    cbDevelopment
    Oct 14, 2004
  4. loveNUNO
    Replies:
    2
    Views:
    949
    loveNUNO
    Nov 20, 2003
  5. EricLondaits
    Replies:
    0
    Views:
    607
    EricLondaits
    May 9, 2007
Loading...

Share This Page