Q

Normalizing a crosstab table

SQL expert Rudy Limeback explains how to use a UNION query to normalize a crosstab table.

I have this table:

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?

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.

This was first published in May 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close