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

["David Johnston" <polobo@xxxxxxxxx>]

 

From: Michael Black [mailto:michaelblack75052@xxxxxxxxxxx]
Sent: Thursday, August 04, 2011 6:42 PM
To: polobo@xxxxxxxxx
Subject: RE: 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.