Post reply

Cross Tab Query Needed

  • October 18, 2007 at 10:50 pm

    #178674

    Hai Friends,

    I am developing a application where, i looking for a solution.

    My scenario is, i have 3 tables which hold the following values

    1) Master Type table - TypeID, TypeName - where TypeID is Primary key column

    eg.

    1,Sales Bill

    2.Receipt Bill

    2) Type Details Table - TypeDetID, TypeID, TypeDetail (multiple for each Type in Master) - where TypeDetID is Primary and TypeId is Foreign Keys

    Eg.

    1,1,Sales Tax

    2,1,Discount

    3,1,Total Value

    3) Transaction Details table which holds the transaction details along with the TypeDetID and Value for TypeDetail (numeric value)

    the scenario is in the transaction details there will be 5 rows if Type details has 5 details defined.

    At runtime the user has to select which details column the user wants to see as a report. Here the user selects the Sales Tax & Total value fields alone.

    eg.

    row1: trans detail with Sales Tax value, Total Value

    row2: trans details with Sales Tax value, Total Value

    row3: trans details with Sales Tax value, Total Value

    Any help will be highly appreciated... 🙂

  • October 19, 2007 at 5:57 am

    #743535

    Nanda,

    I'm not clear on what you want to do? Can you provide expected results?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • October 19, 2007 at 6:06 am

    #743539

    Can u give an example of resultset u require..

  • October 19, 2007 at 6:07 am

    #743541

    pls try the following with your own scenario/funtionality needs.

    $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

    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.

    $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

  • October 20, 2007 at 6:21 pm

    #743884

    You could at least say that you copied all of that from Books Online so the other guy can help himself in the future 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply