Home » SQL & PL/SQL » SQL & PL/SQL » help with matrix query
help with matrix query [message #237272] Mon, 14 May 2007 01:13 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
SQL> desc at
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MEMBER_ID                                          NUMBER(10)
 D1                                                 NUMBER(1)
 D2                                                 NUMBER(1)
 D3                                                 NUMBER(1)
 D4                                                 NUMBER(1)
 D5                                                 NUMBER(1)
 D6                                                 NUMBER(1)
 D7                                                 NUMBER(1)
 D8                                                 NUMBER(1)
 D9                                                 NUMBER(1)
 D10                                                NUMBER(1)
 D11                                                NUMBER(1)
 D12                                                NUMBER(1)
 D13                                                NUMBER(1)
 D14                                                NUMBER(1)
 D15                                                NUMBER(1)
 D16                                                NUMBER(1)
 D17                                                NUMBER(1)
 D18                                                NUMBER(1)
 D19                                                NUMBER(1)
 D21                                                NUMBER(1)
 D22                                                NUMBER(1)
 D23                                                NUMBER(1)
 D24                                                NUMBER(1)
 D25                                                NUMBER(1)
 D26                                                NUMBER(1)
 D27                                                NUMBER(1)
 D28                                                NUMBER(1)
 D29                                                NUMBER(1)
 D30                                                NUMBER(1)

SQL> 

SQL> select member_id,d1,d2 from at;

 MEMBER_ID         D1         D2
---------- ---------- ----------
   2003193          7          7
   2003194          0          0
   2003195          0          0
   2003196          0          0
   2003197          7          7
   2003198          0          0
   2003201          7          7
   2003202          7          7
   2003203          0          0
   2003204          0          0
   2003205          0          0
   2003206          0          0
   2003207          7          7
   2003208          0          0
   2003209          7          7
   2003210          7          7
   2003211          7          7
   2003212          0          0
   2004001          0          0
   2004002          7          7
   2004004          7          7
   2004006          7          7
   2004007          7          7
   2004008          7          7
   2004009          7          0
   2004010          7          7
   2004011          7          7
   2004012          7          7




I need the result like this

Member_ID 	Day	Flag
2003193  	D1	7
2003193  	D2	7
2003194  	D1	0
2003194  	D2	0


Please note I have selected only d1 and d2. I need fields from d1 to d30




Please help me in this regard

Riaz

Re: help with matrix query [message #237279 is a reply to message #237272] Mon, 14 May 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select member_id, 
       decode(rn,1,'D1',2,'D2') day,
       decode(rn,1,d1,2,d2) flag
from at,
     (select rownum rn from dual connect by level <= 2)
/

Regards
Michel
Re: help with matrix query [message #237280 is a reply to message #237272] Mon, 14 May 2007 01:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The easiest would be:
SELECT at_id at_id
     , 'D1'  theday
     , d1    theval
FROM   yourtable
UNION ALL
SELECT at_id at_id
     , 'D2'  theday
     ,  d2   theval
FROM   yourtable
ORDER BY 1,2
/


You don't have to type the entire select. I'd let SQL generate my query:
SQL> SELECT CASE WHEN level > 1
  2         THEN
  3           'UNION ALL '||chr(10)
  4         ELSE
  5           NULL
  6         END
  7      || 'SELECT at_id at_id '||chr(10)
  8      || '     , ''D'||level||''' theday '||chr(10)
  9      || '     , d'||level||' theval'||chr(10)
 10      || 'FROM   yourtable ' theselect
 11  FROM dual
 12  CONNECT BY LEVEL < 31
 13  /

SELECT at_id at_id
     , 'D1' theday
     , d1 theval
FROM   yourtable

UNION ALL
SELECT at_id at_id
     , 'D2' theday
     , d2 theval
FROM   yourtable

UNION ALL
SELECT at_id at_id
     , 'D3' theday
     , d3 theval
FROM   yourtable

UNION ALL
SELECT at_id at_id
     , 'D4' theday
     , d4 theval
FROM   yourtable

UNION ALL
SELECT at_id at_id
     , 'D5' theday
...


MHE
Re: help with matrix query [message #237315 is a reply to message #237272] Mon, 14 May 2007 02:48 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank you, It is OK now...


Riaz
Previous Topic: Tree of Joins ......?
Next Topic: cursor is open or not?
Goto Forum:
  


Current Time: Fri Dec 02 20:40:31 CST 2016

Total time taken to generate the page: 0.07171 seconds