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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.