ColA ColB 2001 2002 2003 ---- ---- ---- ---- ---- vala valb 12 10 11
I need to transform this into a new table like this:
ColA ColB Year Value ---- ---- ---- ----- vala valb 2001 12 vala valb 2002 10 vala valb 2003 11
How can this be done?
Before we jump into the answer, let's take a moment to discuss those column names. Names which are reserved words or which contain special characters are a problem in most database systems. In particular, names which consist entirely of numbers can easily be confused with numeric literals. Therefore, these names need to be quoted or escaped. The standard SQL method is to use doublequotes around the name. Your database system may vary from this method. MySQL, for example, uses backticks (e.g. `2001`), while SQL Server uses square brackets (e.g. ).
The UNION query uses one subselect for each column being extracted. The year value for the new table's Year column is hardcoded as a numeric literal in each subselect.
SELECT ColA , ColB , 2001 AS "Year" , "2001" AS Value FROM daTable UNION ALL SELECT ColA , ColB , 2002 , "2002" FROM daTable UNION ALL SELECT ColA , ColB , 2003 , "2003" FROM daTable
Notice carefully the difference between a numeric literal and a column name. The column alias "Year" has been quoted because YEAR is usually a reserved word (it's a date function). VALUES is a reserved word, but "Value" isn't, so it does not need to be quoted.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading