Home » Tip Bank » Database Development » SQL » PL/SQL » SQL

Language: Enterprise
Expertise: All

Aug 25, 2000

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,
       C.ATTRIBUTE_VALUE UNIT, 
D.ATTRIBUTE_VALUE VALUE
FROM
       PRODUCT_ATTRIBUTES A, 
PRODUCT_ATTRIBUTES B,
       PRODUCT_ATTRIBUTES C, 
PRODUCT_ATTRIBUTES D
WHERE
       A.PRODUCT_CODE = 'P00001' 
AND
       A.ATTRIBUTE_NAME = 'NAME' 
AND
       B.PRODUCT_CODE = A.PRODUCT_CODE 
AND
       B.ATTRIBUTE_NAME = 'SIZE' 
AND
       C.PRODUCT_CODE = A.PRODUCT_CODE 
AND
       C.ATTRIBUTE_NAME = 'UNIT' 
AND
       D.PRODUCT_CODE = A.PRODUCT_CODE 
AND
       D.ATTRIBUTE_NAME = 'VALUE';

The SELF JOIN will return the desired output.