SQL: Dynamic renaming of columns based on row values

Question

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?

@GauravSoni if you didn't like my edit you should have rolled it back. As it is you've just re-introduced grammatical errors and unnecessary in-line tags and removed useful tags? — Ben, Apr 01 '12 at 13:26
@ben:Sorry for that ,actually din't seen your edit part ,may be when i was editing you've commited with your editing .You can revert my changes .Sorry for the inconvinience again. — Gaurav Soni, Apr 01 '12 at 13:45

score 2 · Accepted Answer · answered Apr 02 '12 at 05:22

2

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.

answered Apr 02 '12 at 05:22

Jeffrey Kemp

59,135
14
106
158

We ended up implementing the same thing. Though due to the max column name limitation of oracle, we've created a short alias for some columns and rename those columns before it gets to be displayed in the front end – RavenXV Aug 13 '12 at 00:12

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

You want to execute a dynamic sql statement into a SYS_REFCURSOR

However, you're causing considerable overhead, because

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

edited May 23 '17 at 12:20

Community

1
1

answered Apr 02 '12 at 09:19

HAL 9000

3,877
1
23
29

SQL: Dynamic renaming of columns based on row values

2 Answers2