Sep 29 2010

how do you pivot sql data without aggregating a column

I have the following output in a query.

SKILL                                              LEVEL          SCORERANGE
-----------------------------------------------------------------------------
Stunts                                             LOW            0.0 - 4.0
Stunts                                             MED            3.0 - 7.0
Stunts                                             HI             6.0 - 10.0
Pyramids                                           LOW            0.0 - 4.0
Pyramids                                           MED            3.0 - 7.0
Pyramids                                           HI             6.0 - 10.0
Tosses                                             LOW            0.0 - 4.0
Tosses                                             MED            3.0 - 7.0
Tosses                                             HI             6.0 - 10.0
Standing Tumbling                                  LOW            0.0 - 4.0
Standing Tumbling                                  MED            3.0 - 7.0
Standing Tumbling                                  HI             6.0 - 10.0
Running Tumbling                                   LOW            0.0 - 4.0
Running Tumbling                                   MED            3.0 - 7.0
Running Tumbling                                   HI             6.0 - 10.0
Jumps                                              LOW            0.0 - 4.0
Jumps                                              MED            3.0 - 7.0

I want to PIVOT this data without aggregating anything. So I want a result that shows only one row for each skill and pivots the LEVEL, something like this...

SKILL                                              LOWRANGE       MEDRANGE       HIRANGE 
Stunts                                             0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Pyramids                                           0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Tosses                                             0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Standing Tumbling                                  0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Running Tumbling                                   0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
...

I do have a pretty good understanding of basic pivot syntax but I am struggling with this one because there is nothing to aggregate in the result set.

To give you something to try to fix for me...

SELECT SKILL, LOWRANGE, MEDRANGE, HIRANGE
FROM (SELECT SKILL, [LEVEL], SCORERANGE FROM ScoreRanges) ps
PIVOT 
    (
        MAX(SCORERANGE)  --THIS IS PROBABLY WRONG
        FOR SCORERANGE IN
        (
           --SOMETHING GOES HERE
        )
    ) as pvt

Thanks for your help.

Seth

Sep 29 2010

how do you pivot sql data without aggregating a column

Hello,

I have the following output in a query.

SKILL                                              LEVEL          SCORERANGE
-----------------------------------------------------------------------------
Stunts                                             LOW            0.0 - 4.0
Stunts                                             MED            3.0 - 7.0
Stunts                                             HI             6.0 - 10.0
Pyramids                                           LOW            0.0 - 4.0
Pyramids                                           MED            3.0 - 7.0
Pyramids                                           HI             6.0 - 10.0
Tosses                                             LOW            0.0 - 4.0
Tosses                                             MED            3.0 - 7.0
Tosses                                             HI             6.0 - 10.0
Standing Tumbling                                  LOW            0.0 - 4.0
Standing Tumbling                                  MED            3.0 - 7.0
Standing Tumbling                                  HI             6.0 - 10.0
Running Tumbling                                   LOW            0.0 - 4.0
Running Tumbling                                   MED            3.0 - 7.0
Running Tumbling                                   HI             6.0 - 10.0
Jumps                                              LOW            0.0 - 4.0
Jumps                                              MED            3.0 - 7.0

I want to PIVOT this data without aggregating anything. So I want a result that shows only one row for each skill and pivots the LEVEL, something like this...

SKILL                                              LOWRANGE       MEDRANGE       HIRANGE 
Stunts                                             0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Pyramids                                           0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Tosses                                             0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Standing Tumbling                                  0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Running Tumbling                                   0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
...

I do have a pretty good understanding of basic pivot syntax but I am struggling with this one because there is nothing to aggregate in the result set.

To give you something to try to fix for me...

SELECT SKILL, LOWRANGE, MEDRANGE, HIRANGE
FROM (SELECT SKILL, [LEVEL], SCORERANGE FROM ScoreRanges) ps
PIVOT 
    (
        MAX(SCORERANGE)  --THIS IS PROBABLY WRONG
        FOR SCORERANGE IN
        (
           --SOMETHING GOES HERE
        )
    ) as pvt

Thanks for your help.

Seth

Sep 26 2010

Twitter Weekly Updates for 2010-09-26

Powered by Twitter Tools

Sep 24 2010

Fellow Netflix customer – need help deciding your next Instant Watch? Excel to the rescue. And how I did it.

I am a movie fanatic.  I think movies are serious art and movie-watching is my number one hobby. 

So Netflix is a good fit for me.  And our family watches far more movies using Instant Watch than we do with Blue-Ray and DVD disks.

But in my opinion Netflix has one major weakness.  IT does VERY LITTLE to help you SELECT your next movie.  And I am indecisive which create in me a a little perfect storm of frustration when I am in the mood for a flic.  No kidding, I have spend half the length of a typical movie just trying to FIND the right movie.  

I like to consider a lot of things when I want pic a flix…

  • What GENRE am I in the mood for or is appropriate for the audience?
  • Given the audience (frequently my wife and/or family) what is the MPAA RATING of the movie? 
  • What is the AVERAGE USER RATING of the movie?  (Help me find one that generally doesn’t suck.)

If you want to sort or filter using these criteria it is very difficult. 

To solve this problem I created an Excel spreadsheet to help.  It list the top 30 movies by GENRE sorted by AVERAGERATING in descending order.   Using this you could use the filtering capability of Microsoft Excel to find any movie you want by by Genre, MPAA Rating, and/or User Rating.  

I created an Office 2007 version and an Office 2003 version.  Here they are:

Office 2007 version.

Office 2003 version.

NOW THE TECHNICAL STUFF

To create this spreadsheet I used a FREE awesome querying tool called LinqPad.  You can get it at http://www.linqpad.net/.  After downloading (notice you don’t have to install it…tt is an executable you can put on your desktop.) Put the following C# Linq statements into a new query. 


var genre = "Thrillers";  
var minRating = 3.0;  
var topNum = 30;  

var query =  
    (from g in Genres  
    from t in g.Titles  
    where g.Name==genre  
    && t.Instant.Available==true  
    && t.AverageRating >= minRating   
    orderby t.AverageRating descending   
    select new {t.Name, 
        t.Rating, 
        t.AverageRating, 
        t.ShortSynopsis, 
        Genre=genre}  
    ).Take(topNum);  
    
query.Dump(string.Format("Top {0} {1} Instant Watch Movies with a " +  
                    "{2:0.0} minimum average rating sorted " +  
                    "by average rating in descending order.",  
                    topNum,genre,minRating));  
                    

Be sure that the Language setting is set to “C# Statement(s)” and that the Database is set to “http://odata.netflix.com/Catalog/”.  That is the OData data source that Netflix provides.

Notice that the query can only return results for one Genre at a time.  (That is a limitation of the OData data source.  There are other Netflix Data source types that are not as limiting, but you can’t use those directly in LinqPad without writing a full program.  )  So I had to run the query once for each Genre and aggregate the results into a single spreadsheet.  It took me about an hour.  Keep in mind that the data in the spreadsheet will go stale (as movies are moved out of Instant Watch and new movies are moved in.)  With this LinqPad query you can refresh the data for the genres that interest you the most.

Enjoy.

Seth

Sep 23 2010

Comment by Seth Spearman on With LINQ how do you return a string const in the result from the select operator.

Tomas, that's it. Thanks.
Sep 23 2010

With LINQ how do you return a string const in the result from the select operator

In LinqPad I have the following query statements...

var genre = "Anime & Animation";
var minRating = 3.0;
var topNum = 30;

var query = 
    (from g in Genres
    from t in g.Titles 
    where g.Name==genre
    && t.Instant.Available==true
    && t.AverageRating >= minRating
    orderby t.AverageRating descending 
    select new {t.Name, t.Rating, t.AverageRating, t.ShortSynopsis}).Take(topNum);

query.Dump(string.Format("Top {0} {1} Instant Watch Movies with a {2:0.0} minimum average rating sorted by average rating in descending order.",topNum,genre,minRating));

I want to return the genre string variable in the result set.

Another way to ask, with TSQL I would do "SELECT field1, field2, 'Action & Adventure' as genre from MyTitles". So how do you do that in Linq.

Seth

Sep 23 2010

Comment by Seth Spearman on Visual Studio – how do you use it without touching your mouse?

This is an AWESOME tip. Thanks.
Sep 22 2010

Visual Studio – how do you use it without touching your mouse?

I am going to be doing the codekata defined on Roy Osherove's blog HERE.

One of the rules is that you cannot use the mouse while doing the kata.

Today, my first attempt at doing the kata I have spent the whole time trying to better understand how to use VS without the mouse. I have learned that CTL-ALT-A will be my friend because I can type commands there.

Does somebody have a pointer to a complete reference to the VS Commmands. I want the command name (Edit.ToggleBookmark), command keystroke (like Ctl-K,K), and any arguments required by the command.

Some specific questons I have.

  • Does someone know a keystroke for pinning the active window without using the mouse.
  • Also, I cannot figure out how to add a reference without using the keyboard.

If you can help with those two then I will be significantly farther along.

Thanks.

Seth

edit

Just figured out how to add references. I was working on a project that was not saved and Add References command (Project.AddReference) was returning an error...and I thought it was because I was using the command...but it was actually because I had not saved the project yet.

SO.... if you could help me with the window pinning that would be great.

Seth

EVEN WITH ALL THE HELPS FIGURING OUT COMMANDS...I still cannot figure out how to
- pin a Visual Studio window so it stays open.
- And how do you trigger the context menu any a window. For example, solution explorer?
- How do you delete or remove a file?

EDIT

This StackOverflow question answers the context menu question.

Now...if someone can tell me how to pin a window. That would be awesome.

Seth

Sep 22 2010

Visual Studio – how do you use it without touching your mouse?

Hello,

I am going to be doing the codekata defined on Roy Osherove's blog HERE.

One of the rules is that you cannot use the mouse while doing the kata.

Today, my first attempt at doing the kata I have spent the whole time trying to better understand how to use VS without the mouse. I have learned that CTL-ALT-A will be my friend because I can type commands there.

Does somebody have a pointer to a complete reference to the VS Commmands. I want the command name (Edit.ToggleBookmark), command keystroke (like Ctl-K,K), and any arguments required by the command.

Some specific questons I have.

  • Does someone know a keystroke for pinning the active window without using the mouse.
  • Also, I cannot figure out how to add a reference without using the keyboard.

If you can help with those two then I will be significantly farther along.

Thanks.

Seth

edit

Just figured out how to add references. I was working on a project that was not saved and Add References command (Project.AddReference) was returning an error...and I thought it was because I was using the command...but it was actually because I had not saved the project yet.

SO.... if you could help me with the window pinning that would be great.

Seth

EVEN WITH ALL THE HELPS FIGURING OUT COMMANDS...I still cannot figure out how to
- pin a Visual Studio window so it stays open.
- And how do you trigger the context menu any a window. For example, solution explorer?
- How do you delete or remove a file?

Sep 22 2010

Big companies suck… http://…

Big companies suck... http://tortoisesvn.net/howpaypalscrewsopensourceprojects