Home » SQL & PL/SQL » SQL & PL/SQL » make field value as Header but dynamically
make field value as Header but dynamically [message #388430] Tue, 24 February 2009 13:01 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I used to get the report using MAX(DECODE) to make value as header but user wants this dynamically instead of hard coding header.
Ex.
SELECT   fname, lname, email, Add1, Add2, city, state, zip,Ename,
MAX(DECODE(dept, 'A/c',dept, '')) "A/C",
MAX(DECODE(dept, 'IT',dept, '')) "IT",
MAX(DECODE(dept, 'Phd,dept, '')) "Phd",
MAX(DECODE(dept, 'Maths',dept, '')) "Maths",
....
From
(
SELECT  fname, lname, email, Add1, Add2, city, state, zip,Ename, DEPT
     FROM Table Emp A, dept B,...
Where
A.id = B.id
AND...
AND...
)
GROUP BY fname, lname, email, Add1, Add2, city, state, zip, Ename, DEPT

But i don't want to hard code all this dept as it might possibility to add more dept so i want to make it dynamically.

your help will be greatly appreciated!

thanks,
Re: make field value as Header but dynamically [message #388434 is a reply to message #388430] Tue, 24 February 2009 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please SEARCH BEFORE posting, this has been asked 3 or 4 times in the last 2 weeks.

Regards
Michel
Re: make field value as Header but dynamically [message #388917 is a reply to message #388434] Thu, 26 February 2009 11:34 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi Michel,
Thanks for pointing to the right url and it has nice information.
I found the following code that will help me out but i tried to implement in my existing code but having trouble as i am joining lots of tables and using lots of columns for display in my out put.
If you guide me, really appreciated.

I found useful from your link:
CREATE TABLE TESTSS
    (
      CUST	 NUMBER,
      ID	 NUMBER,
      TAX_CODE  NUMBER,
      CITY	 VARCHAR2(9)
    );

Table created.
 BEGIN
      INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
      123, 2, 222, 'Bangalore');
      INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
      123, 3, 333, 'Delhi');
      INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
      123, 4, 444, 'Mysore');
      INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
      124, 2, 222, 'Bangalore');
     INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
     124, 3, 333, 'Mysore');
     INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
     125, 4, 444, 'Mysore');
     COMMIT;
   END;
   /

PL/SQL procedure successfully completed.

 SET AUTOPRINT ON
 VARIABLE g_results REFCURSOR
 DECLARE
      v_cities  NUMBER;
      v_sql	 VARCHAR2 (32767);
    BEGIN
      SELECT MAX (COUNT (city)) INTO v_cities FROM testss GROUP BY cust;
      v_sql := 'SELECT cust';
      FOR i IN 1 .. v_cities LOOP
    	 v_sql := v_sql || ', MAX (DECODE (rows1, ' || i || ', city))	  city' || i;
    	 v_sql := v_sql || ', MAX (DECODE (rows1, ' || i || ', tax_code)) code' || i;
     END LOOP;
     v_sql := v_sql ||
   	 ' FROM  (SELECT cust, tax_code, city,
   			 ROW_NUMBER () OVER (PARTITION BY cust ORDER BY city) rows1
   		  FROM	 testss)
   	   GROUP BY cust';
     OPEN :g_results FOR v_sql;
   END;
   /

PL/SQL procedure successfully completed.


      CUST CITY1          CODE1 CITY2          CODE2 CITY3          CODE3
---------- --------- ---------- --------- ---------- --------- ----------
       123 Bangalore        222 Delhi            333 Mysore           444
       124 Bangalore        222 Mysore           333
       125 Mysore           444




My code:
SELECT  s_name ,login, f_name, l_name, email, S_ID, S_EMAIL,
desc, wave, 
(SELECT	DISTINCT
	',	COUNT (CASE WHEN C_CODE = '''
||	C_CODE
||	''' '	AS txt1
,	'THEN 1 END)	AS '
||	C_CODE
||	'_CNT'	AS txt2
FROM	C_CORS H1
ORDER BY	txt1)
FROM 
(
SELECT  b.S_name, d.login, d.f_name,d.l_name, d.email,
(SELECT d.login FROM user d WHERE d.id = c.rep_to_id) AS S_ID,
(SELECT d.email FROM user d WHERE d.id = c.rep_to_id) AS S_EMAIL,
e.desc, f.wave, C_CODE, S_TASK
     FROM CT_E_STAK_MAP  	A,
          ct_stak 		B,
          e_info 		C,
          user 			D,
          e_code 		E,
          e_web_info 		F, 
          C_STAK_MAP 		G,
          C_CORS 		H, 
          C_S_TASK 		I,
          C_S_STAK_MAP 		J
WHERE a.C_stak_id 	= b.c_stak_id 
AND   b.c_stak_id 	= G.c_stak_id
AND   B.C_STAK_ID 	= J.C_STAK_ID
AND   J.C_S_TASK_ID 	= I.C_S_TASK_ID
AND   G.C_CORS_ID      	= H.C_CORS_ID
AND   a.U_id         	= c.U_id 
AND   a.U_id         	= d.id 
AND   d.id              = f.U_id 
AND   c.ctll3 		= e.c_st_val 
AND   c.ctl1_2 		= e.ctl1_2 
AND   e.c_set_name 	= 'EY' 
AND   c.active		= 1
GROUP BY b.s_name, d.login, d.f_name,d.l_name, d.email,d.login, d.email, S_ID, S_EMAIL,
 e.desc, f.wave, C_CODE, S_TASK
)
GROUP BY s_name ,login, f_name, l_name, email, S_ID, S_EMAIL, desc, wave



Thanks once again for your time and help!
Previous Topic: Hierargical query
Next Topic: Please help
Goto Forum:
  


Current Time: Mon Dec 05 21:14:49 CST 2016

Total time taken to generate the page: 0.08542 seconds