Re: Is there a better way to unnest an entire row?

From: Michael Black [mailto:michaelblack75052(at)hotmail(dot)com]
Sent: Thursday, August 04, 2011 6:42 PM
To: polobo(at)yahoo(dot)com
Subject: RE: [GENERAL] Is there a better way to unnest an entire row?

I do not see where you would need to do a self-join on the table. However,
a union would work nice to accomplish your desired goal.
However, I would think about normalizing the table at some point in the
future.

select id, item_name, item_value from
(
select id, item_name as item1_name, item1_value as item_value
union
select id, item_name as item2_name, item2_value as item_value
)
order by id

This has not be tested.

_____

FWIW the table is generated from an external source that itself is
de-normalized AND from which I can only import the data using CSV. The
reason I am asking the question and writing the query is because "I AM
NORMALIZING THE DATA", Whether I leave it in a VIEW or materialize it into
a table is irrelevant (other than performance concerns).

Having written the ARRAY version of the query, and while pondering the UNION
version, my instinct says that the UNION version would likely perform
better.

Either way, and no offense, but I was hoping for some outside-the-box ideas
as opposed to the obvious UNION implementation you suggested (which is why I
mentioned that I could do the UNION version in my original e-mail).

Thanks,

David J.