Query problem

Question

Post reply

Query problem

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

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

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 1

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]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 4

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

Thanks

Ahmad

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 6

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]