CODEWITHSUNDEEP
sqloracle-databaseoracle10g
RavenXV
RavenXV

Reputation: 377

SQL: Dynamic renaming of columns based on row values

Considering Oracle 10g, is there a way to rename a column based on specified rows?

Let me first give a background. The requirement was to pivot rows and turn them to columns. Since Oracle 10g doesn't support the function PIVOT, we've done a work around using max and case keywords.

Now the problem is this. Is there a way to rename a column based on two rows? Considering the tables below:

BRAND   | MODEL  | COMPONENT_NAME | COMPONENT_VALUE | ATTRIBUTE_NAME | ATTRIBUTE_VALUE
-----------------------------------------------------------------------------------
SAMSUNG | I9100  | Chipset        | Exynos          | frequency      | 1200     
SAMSUNG | I9100  | Screen         | Amoled          | colors         | 16M  
SAMSUNG | I9100G | Chipset        | TI OMAP4430     | frequency      | 1200     
SAMSUNG | I9100G | Screen         | Amoled          | colors         | 16M 
------------------------------------------------------------------------------------

We want this: with the COMPONENT_NAME above transformed as the `column header for one and theCOMPONENT_NAME - ATTRIBUTE_NAME` as for another.

BRAND   | MODEL  | Chipset     | Chipset - frequency | Screen | Screen - colors
------------------------------------------------------------------------
SAMSUNG | I9100  | Exynos      | 1200                | Amoled | 16M        
SAMSUNG | I9100G | TI OMAP4430 | 1200                | Amoled | 16M
------------------------------------------------------------------------

currently we do the following to produce the second table:

SELECT DISTINCT BRAND, MODEL,
MAX(CASE WHEN (COMPONENT_NAME = 'Chipset') THEN
COMPONENT_VALUE
END) AS "Chipset",
MAX(CASE WHEN (COMPONENT_NAME = 'Chipset' and ATTRIBUTE_NAME = 'frequency') THEN
ATTRIBUTE_VALUE
END) AS "Screen",
MAX(CASE WHEN (COMPONENT_NAME = 'Screen') THEN
COMPONENT_VALUE
END) AS "Screen",
MAX(CASE WHEN (COMPONENT_NAME = 'Screen' and ATTRIBUTE_NAME = 'colors') THEN
ATTRIBUTE_VALUE
END) AS "Screen - colors" from table....etc.

Is there a way to dynamically name the column?

Upvotes: 1

Views: 2585

Answers (2)

HAL 9000
HAL 9000

Reputation: 3985

You want to execute a dynamic sql statement into a SYS_REFCURSOR

However, you're causing considerable overhead, because

  1. you'd have to execute the query twice (1st to build the dynamic SQL, 2nd to retrieve the result)
  2. you're hard-parsing more often than would be neccessary

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

A column name is analogous to a variable name - it's an identifier that may be used in a program. It doesn't make much sense for its name to change dynamically.

Your current strategy of having different columns for each attribute is ok, IMO.

You are experiencing the downside to having an EAV data model.

Upvotes: 2

Related Questions