devxlogo

Showing Rows of a Table in Columns Format

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_VALUEP00001              	NAME                    	PRODUCT NAME 1P00001              	SIZE                    	10 * 5P00001              	VALUE                   	10,000P00001              	UNIT                    	FT

Now we can retrieve this info in only one row like this:

 PRODUCT_ID	  NAME            	SIZE	UNIT	VALUEP00001       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 VALUEFROM PRODUCT_ATTRIBUTES A,
PRODUCT_ATTRIBUTES B, PRODUCT_ATTRIBUTES C,
PRODUCT_ATTRIBUTES DWHERE 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.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist