|
This can be accomplished with a UNION query.
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. [2001]).
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.
|
