Sometimes in our database structures we've got to store information in such a
way that it allows us to increase/decrease the type of data we store. For
example, we have a PRODUCTS table and a PRODUCTS_ATTRIBUTES table. The
PRODUCTS_ATTRIBUTES table stores the attributes of a Product that is defined
in say the PRODUCTS table.
The PRODUCTS_ATTRIBUTES table has got only three columns PRODUCT_ID,
ATTRIBUTE_NAME, and ATTRIBUTE_VALUE. Now let's say there are a certain number of common attributes for each product, which have ATTRIBUTE_NAME as NAME, SIZE, VALUE, UNIT_OF_MEASURE, etc. All these attributes are each stored in a separate row inside the PRODUCT_ATTRIBUTES table like this:
PRODUCT_ID ATTRIBUTE_NAME ATTRIBUTE_VALUE
P00001 NAME PRODUCT NAME 1
P00001 SIZE 10 * 5
P00001 VALUE 10,000
P00001 UNIT FT
Now we can retrieve this info in only one row like this:
PRODUCT_ID NAME SIZE UNIT VALUE
P00001 PRODUCT NAME 1 10 * 5 FT 10,000
We write a procedure, have a cursor, and (in Oracle) define a user object
with all these attributes. Then we fill it inside the procedure and send it
back to the user. However, that's quite a lengthy way.
There is another way you can do it using only one query:
SELECT A.ATTRIBUTE_VALUE NAME, B.ATTRIBUTE_VALUE SIZE,
A.PRODUCT_CODE = 'P00001'
A.ATTRIBUTE_NAME = 'NAME'
B.PRODUCT_CODE = A.PRODUCT_CODE
B.ATTRIBUTE_NAME = 'SIZE'
C.PRODUCT_CODE = A.PRODUCT_CODE
C.ATTRIBUTE_NAME = 'UNIT'
D.PRODUCT_CODE = A.PRODUCT_CODE
D.ATTRIBUTE_NAME = 'VALUE';
The SELF JOIN will return the desired output.