[KLUG Members] Data transformation

[Andrew Eidson]

Well that would have worked.. except it does not allow Null values and there
are some fields with Nulls.. 

-----Original Message-----
From: members-bounces at kalamazoolinux.org
[mailto:members-bounces at kalamazoolinux.org] On Behalf Of Adam Tauno Williams
Sent: Friday, September 23, 2005 11:36 AM
To: The main KLUG mailing list.
Subject: Re: [KLUG Members] Data transformation

> I am taking data from a SQL database that is long, below is an example.. 
> User_id Field_id Field_value
> 1        1         data1
> 1        2         data2
> 1        3         data3
> 1        4         data4
> 2        1         data1
> 2        2         data2
> 2        3         data3
> 2        4         data4
> What I need to do is export it but into a wide file so that it looks like
> the following
> 1, data1, data2, data3, data4
> 2, data1, data2, data3, data4
> This will be used for both Postgres databases and MS SQL databases.. my
main
> question is.. Can this be done with a SQL query or will the data have to
be
> exported Long and then run through a program/script to change it to the
wide
> format? Everything I have tried from an SQL stand point has not worked..
but
> I am not strong with SQL queries right now. 

In PostgreSQL at least you can do this easily in SQL.
http://www.postgresql.org/docs/7.4/interactive/arrays.html

I'm certain M$-SQL offers something equivalent.