October 28th, 2012, 06:03 PM
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.
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
I can group by phone number with this:
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.
SELECT SUBSTR(PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(PHONE_NUMBER, 1, 3)) TOTAL_COUNT
GROUP BY SUBSTR(PHONE_NUMBER, 1, 3);
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:
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;
October 28th, 2012, 11:41 PM
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.
October 28th, 2012, 11:46 PM
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.
October 29th, 2012, 01:23 AM
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.
October 29th, 2012, 02:07 AM
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.
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;
October 29th, 2012, 03:07 AM
That's not possible.
Originally Posted by miguelosaurio
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.
Tips on how to ask better questions: