SQL Query: Switching Rows to Columns dynamically.

From: Anil G <anil.gothal_at_gmail.com>
Date: 16 Oct 2006 15:45:11 -0700
Message-ID: <1161038711.676290.252870@i3g2000cwc.googlegroups.com>

Guys:

I am looking to develop SQL query which will switch the rows to Columns. I was trying with DECODE statements but that leads to hardcoding of types. Any one has any clues to develop dynamic logic to convert rows to columns. Following is detailed information about problem.

Thanks in advance,
Anil G

• problem statement

  ---

  type_tbl
  + name
  

data_tbl
+ org
+ type_name
+ data_value (FK to type_tbl)

---

Data layouts: type_tbl, user can keep adding types from the application.

type_tbl

---

T-1
T-2
T-3
T-4

..
..

data_tbl

---

org			type_name 	data_value
----		---------		-----------
O-1			T-1						100
O-1			T-2						30
O-1			T-3						45
O-2			T-1						100
O-2			T-4						30
O-3			T-1						45

Desired Output:

---

org	T-1	T-2	T-3	T-4 .....
--- ---	---	---	---	--
O-1	100	30	45
O-2	100	0		0		25
O-3	45

--------------------------------------------------------- Ends
-------------------------------------------------------------

Received on Mon Oct 16 2006 - 17:45:11 CDT