how to transform vertical fields in a table to horizontal result by SQL

Question

I have a table like this:

create table t1 {
  person_id int,
  item_name varchar(30),
  item_value varchar(100)
};

Suppose person_id+item_name is the composite key, now I have some data (5 records) in table t1 as below:

person_id ====item_name ====== item_value
   1          'NAME'           'john'
   1          'GENDER'         'M'
   1          'DOB'            '1970/02/01'
   1          'M_PHONE'        '1234567890'
   1          'ADDRESS'        'Some Addresses unknown'

Now I want to use SQL (or combing store procedure/function or whatever) to query the above result (1 result set) become:

NAME==GENDER==DOB========M_PHONE=======ADDRESS===============
1     M       1970/02/01 1234567890    Some Addresses unknown

How should I do ? Thank you for your help.

Answer 1

Regardless of the database you are using, the concept of what you are trying to achieve is called "Pivot Table".

Here's an example for mysql: http://en.wikibooks.org/wiki/MySQL/Pivot_table

Some databases have builtin features for that, see the links below.

SQLServer: http://msdn.microsoft.com/de-de/library/ms177410.aspx

Oracle: http://www.dba-oracle.com/t_pivot_examples.htm

You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set. Note, in your case, you can put all the names into one column using concat (i think that's group_concat in mysql), since you cannot know how many names are related to a person_id.

Answer 2

Finally, I found the solution in PostgreSQL:

select * from crosstab ('select person_id,  item_name, item_value from t1 where person_id = 1 ') 
as virtual_table ( person_id integer, name varchar, gender varchar, dob varchar, m_phone varchar, address varchar)

Also need to install the crosstab function on Postgres. See more: http://www.postgresql.org/docs/8.3/static/tablefunc.html