Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jaroot
Starting Member
46 Posts |
Posted - 2005-09-01 : 23:26:23
|
I have a situation where I need to return X # of columns back in a result set, calculating certain averages based on how many records exist for the column.Lets use Movie Rentals as an example...DATA:tMovieTypes(just say for the sake of arguement that this list could be infinate)TYPEID - TYPE1 - ACTION2 - DRAMA3 - CHILDRENS4 - ADULT5 - COMEDY6 - FOREIGN....tRenterRENTERID - TYPEID - DATEOUT - DATEIN1 - 1 - 9/1/05 - 9/2/05 1 - 2 - 8/1/05 - 8/2/051 - 1 - 7/1/05 - 2/2/052 - 4 - 6/1/05 - 6/3/052 - 4 - 5/1/05 - 5/3/052 - 4 - 4/1/05 - 4/3/053 - 3 - 3/1/05 - 3/2/053 - 5 - 2/1/05 - 2/2/05 I need to bring back the following recordset:COLUMNS --all of the types from tMovieTypes (including those that dont have rentals associated with them (i.e. FOREIGN)--and the avg rental time (in days) that the specific typewas kept.. so...ACTION - DRAMA - CHILDRENS - ADULT - COMEDY - FOREIGN 2 1 1 3 1 0AVGACT - AVGDRA - AVGCHI - AVGADU - AVGCOM - AVGFOR 1 1 1 2 1 0 I'm trying to a bunch of stuff with cursors and generating system tables to store the results.. but It's getting kinda convoluted.. I need someone to point me in the direction for a simple solution. Any help would be GREATLY appreciated!Thanks,SQL NOVICE NOOOB |
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-02 : 05:44:48
|
Don't bring the answer back as columns...bring it back as rows...1 poer movie type.and if necessary transpose it at the presentation layer. |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-02 : 09:00:30
|
It is called a cross tab or pivot table and you can do it directly at the DB level. Here is the BOL article..."Cross-Tab ReportsSometimes 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.11990 2 1.21990 3 1.31990 4 1.41991 1 2.11991 2 2.21991 3 2.31991 4 2.4A 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 NorthwindGOCREATE TABLE Pivot( Year SMALLINT, Quarter TINYINT, Amount DECIMAL(2,1) )GOINSERT 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)GOThis 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 Q4FROM Northwind.dbo.PivotGROUP BY YearGOThis 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 YearTotalFROM (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 P1GOBoth 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"====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
jaroot
Starting Member
46 Posts |
Posted - 2005-09-02 : 10:17:45
|
This would work awesome if I knew how many "Quarters" I was dealing with.. but it could be 1-n # of different "Movie Types".. (and yes I know there are probably a finite # of movie types, but for what I'm using it for it could be unlimited) |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
jaroot
Starting Member
46 Posts |
Posted - 2005-09-02 : 10:22:30
|
quote: Originally posted by AndrewMurphy Don't bring the answer back as columns...bring it back as rows...1 poer movie type.and if necessary transpose it at the presentation layer.
Not a bad idea.. didn't think of that. |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-02 : 11:14:10
|
You might find my series of posts about crosstabs here helpful as well:http://weblogs.sqlteam.com/jeffs/archive/2005/05/15/5175.aspxnot as organized as I would like, but it covers lots of different ways of doing this. The key thing to remember is, crosstabbing is a presentation issue, not a database issue and it should be avoided within T-SQL whenever possible. Your presentation layer (whatever it may be) is almost always better equiped to crosstab results more efficiently and easily than within sql. |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
|
|
|
|
|