[KLUG Members] Data transformation

[Andrew Thompson]

On Fri, 2005-09-23 at 11:13 -0400, Andrew Eidson wrote:
> Hello all.. 
> 
> 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. 

Try this:

select tbl.User_id, a.Field_Value, b.Field_Value, c.Field_Value,
d.Field_Value from tbl
left join (select User_id, Field_Value from tbl where Field_id=1) as a
on tbl.User_id = a.User_id 
left join (select User_id, Field_Value from tbl where Field_id=2) as b
on tbl.User_id = b.User_id 
left join (select User_id, Field_Value from tbl where Field_id=3) as c
on tbl.User_id = c.User_id 
left join (select User_id, Field_Value from tbl where Field_id=4) as d
on tbl.User_id = d.User_id;

I know it's long, and looks complicated, but it's about what you have to
do in a situation like this. It's really not good design to use a single
column to store multiple facts about an entity like this, if they're
going to need to be split out to their own separate columns anyway.

I'm not 100% sure this query will work as written, but I THINK MySQL and
Postgres should handle nested queries all right. Even MS Access does,
though you get sneaky about it. Obviously, if you have a LOT of
different Field_id values, you're going to need a much longer list of
subqueries, and there may be some limit to how many you can stuff into
one operation, but you may want to test out a short version like this
one, first.

Good luck!

-- 
Andrew Thompson <tempes at ameritech.net>
The Imagerie