#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    7
    Rep Power
    0

    How to name a column from data stored in certain row?


    Hello, thank you for reading this and trying to help me, I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have googled but I think the query is a little too complicated for me to explain it and get a result from google or find it in a section of a book.

    My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.

    All help would be greatly appreciated, thanks in advance.



    Edit:

    I am trying to learn by myself how to use databases and am using the HR database that comes in oracle since I read in a forum that it is a great way to learn how to use queries.

    I read about using Pivot to do it, but in pivot I need to know how many area codes there are. I will post a screen shot of the table that I am working on the other post

    http://puu.sh/1jVVx

    I can group by phone number with this:

    Code:
    SELECT SUBSTR(PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(PHONE_NUMBER, 1, 3)) TOTAL_COUNT 
    FROM EMPLOYEES 
    GROUP BY SUBSTR(PHONE_NUMBER, 1, 3);
    But what I am interested in doing is that instead of Area appearing as a column, that the actual first 3 digits would appear as the column name.


    Edit 2:
    This is kind of what I am trying to do, but instead of AREA appearing as column name I would like for each one of the area codes to appear as column name, here is the code:

    Code:
    SELECT DE.DEPARTMENT_NAME, EM.SEX, SUBSTR(EM.PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(EM.PHONE_NUMBER, 1, 3)) TOTAL_COUNT FROM EMPLOYEES EM, DEPARTMENTS DE WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME, EM.SEX, SUBSTR(PHONE_NUMBER, 1, 3) ORDER BY DEPARTMENT_NAME;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I don't know if anyone will give you a solution; but let me assure you it is a dreadful table design.
    Before trying to learn anything else about SQL databases please read a book or find some articles
    about the Relational Model and Normal Forms.
    It is the best thing you can do for yourself as you start this journey of discovery.

    Clive
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    7
    Rep Power
    0
    The design was created by oracle and it comes by default when you download it, regardless of the design I have read some books and I am more intrigued in doing this, it's something that came to my head just testing the limits of oracle just testing the waters since it's my last year of high school and I am still figuring out my college education.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I am all in favor of you trying out ideas; but please read some books on the theory of relational databases ASAP
    if you are serious about learning anything about designing database schema.

    Clive
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    7
    Rep Power
    0
    Oh no I am totally taking your advice because I am new to this, but in my country, most databases are not made very well and some people don't even know how normalization works and in most cases people will hire you to do a hot fix to their database rather than spend much more money on you making a new one.

    And I kind of solved it in case it helps anyone else both pivot and decode work in this case and I will be posting my code in case it helps anyone else, it might not be the most proper way to handle it, but hey I am learning I am sure there is a cleaner way to do it.

    Code:
    SELECT * FROM ( SELECT DE.DEPARTMENT_NAME, EM.SEX, SUBSTR(EM.PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(EM.PHONE_NUMBER, 1, 3)) TOTAL_COUNT FROM EMPLOYEES EM, DEPARTMENTS DE WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME, EM.SEX, SUBSTR(PHONE_NUMBER, 1, 3) ) PIVOT (SUM(TOTAL_COUNT) FOR (AREA) IN ('011' AS A_101, '515' AS A_515, '590' AS A_590, '603' as A_603, '650' as A_650)) ORDER BY DEPARTMENT_NAME;
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by miguelosaurio
    But what I am interested in doing is that instead of Area appearing as a column, that the actual first 3 digits would appear as the column name.
    That's not possible.

    The column names must be known before the statement is executed.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

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

IMN logo majestic logo threadwatch logo seochat tools logo