dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: Enterprise
Expertise: All
Aug 25, 2000

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


Showing Rows of a Table in Columns Format

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.
Jatin Shah
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date