Post reply

Query problem

  • January 23, 2008 at 2:51 am

    #770801

    Hi,

    Thanx for the help.....I was running sql 2005 express edition client....but the server version is 2000.

    I already worked out the given solution from BOL......

    Thanks any ways......

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • January 23, 2008 at 5:18 am

    #770827

    Would you share your solution, please?

    --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

  • January 23, 2008 at 5:38 am

    #770834

    Wow... that's pretty rude... I just found your other 10 cross posts that you posted every 30 to 60 minutes.

    --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

  • January 23, 2008 at 9:56 pm

    #771148

    Hi,

    That was not rude...I acknowledged the efforts of each and every person who contributed to my post.....

    I just said that i got that very solution in books online ..........also I appreciated their efforts.

    Below is the code that I used......

    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.

    See Also

    SELECT

    ©1988-2000 Microsoft Corporation. All Rights Reserved.

    Thanks

    Ahmad

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • January 24, 2008 at 4:28 pm

    #771543

    That was not rude...I acknowledged the efforts of each and every person who contributed to my post.....

    No, it's downright rude when you post the same question on 10 different forums on the same site... people can't see where you may already have a good solution and it divides resources.

    Thanks for posting your solution... on just one of the threads 😉

    --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

  • January 24, 2008 at 9:04 pm

    #771575

    Hi,

    Someone one sujessted me that I am in wrong forum...ishud post my problem in sql 2000 forum....so i did that only....after my post in onther.

    Any ways thanks for the valuable information..i will not post my problem in more than one forum..

    Regards,

    Ahmad

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

Viewing 6 posts - 16 through 20 (of 20 total)

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