Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: Help with a query please

    I'm using postgres as my database and I'm trying to create a radar chart from some data I have. Guess I'm not as good at SQL as I had hoped and could use some help please. Let me explain....

    My data looks like this...

    Player | PassingAtt | PassingCmp | RushingAtt
    Player1 XX XX XX

    These are the names of columns. Player is a test field and the others are values for these categories.

    The radar chart needs its data like this...

    Serie | Category | Value
    Player1 | PassingCmp | xx
    Player1 | PassingAtt | xx
    Player1 | PassingTD | xx
    Player1 | PassingInt | xx
    Player2 | PassingCmp | xx
    Player2 | PassingAtt | xx
    Player2 | PassingTD | xx
    Player2 | PassingInt | xx

    How do I format my query to make my data look more like the above please?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Code:
    select Player as serie, 
           'PassingAtt' as category,
           PassingAtt as value
    from the_table
    union all
    select Player as serie, 
           'PassingCmp' as category,
           PassingCmp  as value
    from the_table
    union all
    select Player as serie, 
           'RushingAtt' as category,
           RushingAtt  as value
    from the_table
    As you apparently use case-sensitive column names (because you posted a column name of Player, rather than player), the columns most probably need to be references using double quotes, e.g. "Player", rather than Player. But as you didn't provide the DDL statement for your table this is impossible to say.

    For more details on why this is necessary please see the manual: http://www.postgresql.org/docs/curre...AX-IDENTIFIERS

    If you did create the table using double quotes around your column names, I would strongly suggest you rethink that approach. Using case-sensitive column names is usually much more trouble than it's worth.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Oct 2013
    Posts
    2
    Quote Originally Posted by shammat View Post
    Code:
    select Player as serie, 
           'PassingAtt' as category,
           PassingAtt as value
    from the_table
    union all
    select Player as serie, 
           'PassingCmp' as category,
           PassingCmp  as value
    from the_table
    union all
    select Player as serie, 
           'RushingAtt' as category,
           RushingAtt  as value
    from the_table
    As you apparently use case-sensitive column names (because you posted a column name of Player, rather than player), the columns most probably need to be references using double quotes, e.g. "Player", rather than Player. But as you didn't provide the DDL statement for your table this is impossible to say.

    For more details on why this is necessary please see the manual: PostgreSQL: Documentation: 9.3: Lexical Structure

    If you did create the table using double quotes around your column names, I would strongly suggest you rethink that approach. Using case-sensitive column names is usually much more trouble than it's worth.
    Thank you very much. This was a big help and exactly what I need

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try this.

    Code:
    SELECT Player AS serie
         , category
         , CASE category
           WHEN 'PassingAtt' THEN PassingAtt
           WHEN 'PassingCmp' THEN PassingCmp
           WHEN 'RushingAtt' THEN RushingAtt
           END  AS value
     FROM  the_table
     CROSS JOIN
           (VALUES ('PassingAtt') , ('PassingCmp') , ('RushingAtt')
           ) AS p(category)
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •