[KLUG Members] Data transformation

[Adam Tauno Williams]

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

Do the NULLs actually mean something as a value?  (Which is sort of 
contrary to
the concept of NULL).  Can you perhaps substitute some value for the 
NULL using
COALESCE?

SELECT company_id,
COALESCE(is_person, 0) AS is_person
FROM company

This replaces the value of the first parameter with the value of the second
parameter if the first parameter is not a value (i.e. IS NULL).  This should
work in Orable as well, and the equivalent in Informix is NVL(x,x);  I don't
know what it is in M$-SQL but ever database I've spent much time in has an
equivalent.

This is very useful in situations like where a presence of "1" in is_person
means a record is a contact and an absence indicates it is not a contact - but
due to variations in the code base some applications set is_person to 1 or 0
and others set it to 1 if it is true and completely ignore it otherwise (leave
it NULL).

> -----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.
>
> _______________________________________________
> Members mailing list
> Members at kalamazoolinux.org
> 
>



-- 
Adam Tauno Williams - http://www.whitemice.org