Re: Need sql to pull data from terribly architected table

On Tue, Oct 23, 2012 at 2:52 PM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com>wrote:

> Here's the deal... ****
>
> ** **
>
> Instead of architecting and loading a table like... ****
>
> create teble foo (col1 text, col2 text, col3 text, col4 text, col5 text);
> ****
>
> insert into foo (col1,col2,col3,col4,col5) values
> ('c1',null,'c3','c4',null);****
>
> ** **
>
> They did this instead...****
>
> ** **
>
> create table foo (property text, value text);****
>
> insert into foo (property, value) values ('col1','c1'), ('col3','c3'),
> ('col4','c4');****
>
> ** **
>
> Notice how "col2" and "col5" were simply left out of the table in the 2nd
> model to indicate null.****
>
> ** **
>
> The question is how to do this model 1 query for model 2...****
>
> ** **
>
> select col1,col2 from foo where col4='c4' and col5 <> 'xxx';****
>
> ** **
>
> I know I have to use outer joins to deal with the potential of nulls. But
> I don't know how to construct this. I won't list my failed attempts (so as
> not to embarass myself :-))****
>
> ** **
>
> Thanks in Advance !****
>
> ** **
>
> ** **
>

assuming there is some other column (I'll call it "id") that tells you
which rows in foo go together, then this is a two-stepper:

1) Get the list of all the ids

create temporary table my_ids (id integer);

insert into my_ids (id)
select distinct id from foo;

2) Now go back and get the values

select my_ids.id
, c1.value as col1
, c2.value as col2
, c3.value as col3
, c4.value as col4
, c5.value as col5
from my_ids
left join foo c1 on my_ids.id = c1.id
left join foo c2 on my_ids.id = c2.id
left join foo c3 on my_ids.id = c3.id
left join foo c4 on my_ids.id = c4.id
left join foo c5 on my_ids.id = c5.id

--
e-Mail is the equivalent of a postcard written in pencil. This message may
not have been sent by me, or intended for you. It may have been read or
even modified while in transit. e-Mail disclaimers have the same force in
law as a note passed in study hall. If your corporate attorney says that
you need an disclaimer in your signature, you need a new corporate attorney.