Home » SQL & PL/SQL » SQL & PL/SQL » rows as columns in sql
rows as columns in sql [message #209015] Tue, 12 December 2006 23:15 Go to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member

I get some common rows in Table1
I want those as columns

Example :

TestTable1
----------
ItemID TestNo TestDesc TestValue
001 1 T1 V1
001 1 T2 V2
001 1 T3 V3
001 2 T1 V4
001 2 T2 V5
001 2 T3 V6
001 3 T1 V7
001 3 T2 V8
001 3 T3 V9
001 4 T1 V10
001 4 T2 V11
001 4 T3 V12
001 5 T1 V13
001 5 T2 V14
001 5 T3 V15

I want output from the sql like the following

ItemID TestNo T1 T2 T3
------------------------------------
001 1 V1 V2 V2
001 2 V4 V5 V6
001 3 V7 V8 V9
001 4 V10 V11 V12
001 5 V13 V14 V15

note: columns t1, t2, t3 are the row results from testtable1

is it possible?

thanks.





Re: rows as columns in sql [message #209046 is a reply to message #209015] Wed, 13 December 2006 01:25 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Some tips:
  • Oracle version would be nice
  • Look around for PIVOT queries (This site has a search button).
  • Provide CREATE TABLE + INSERT statements so we can create a test set

But it has been some time since I last answered this one so here goes.

A small note before we begin. A SQL query needs to know in advance what you select (how many and what columns you are selecting). If you have a fixed set (ItemID TestNo, T1, T2, T3 ) there's no problem. But if you have no fixed set and the number of "T columns" is variable, you cannot use plain sql I'm afraid.

My script (the part before select is to create a test set):
WITH yourtable AS 
( 
  SELECT '001' itemid, 1 testno, 'T1' testdesc, 'V1 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 1 testno, 'T2' testdesc, 'V2 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 1 testno, 'T3' testdesc, 'V3 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 2 testno, 'T1' testdesc, 'V4 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 2 testno, 'T2' testdesc, 'V5 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 2 testno, 'T3' testdesc, 'V6 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 3 testno, 'T1' testdesc, 'V7 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 3 testno, 'T2' testdesc, 'V8 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 3 testno, 'T3' testdesc, 'V9 ' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 4 testno, 'T1' testdesc, 'V10' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 4 testno, 'T2' testdesc, 'V11' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 4 testno, 'T3' testdesc, 'V12' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 5 testno, 'T1' testdesc, 'V13' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 5 testno, 'T2' testdesc, 'V14' testvalue FROM dual UNION ALL
  SELECT '001' itemid, 5 testno, 'T3' testdesc, 'V15' testvalue FROM dual 
)
SELECT itemid
     , testno
     , MAX(DECODE(testdesc, 'T1', testvalue, NULL)) t1
     , MAX(DECODE(testdesc, 'T2', testvalue, NULL)) t2
     , MAX(DECODE(testdesc, 'T3', testvalue, NULL)) t3
FROM   yourtable
GROUP  BY itemid
        , testno

/

The run:
SQL> @orafaq

ITE     TESTNO T1  T2  T3
--- ---------- --- --- ---
001          1 V1  V2  V3
001          2 V4  V5  V6
001          3 V7  V8  V9
001          4 V10 V11 V12
001          5 V13 V14 V15

SQL>
MHE

Previous Topic: How to find dependecies on a constraint
Next Topic: number to char conversion
Goto Forum:
  


Current Time: Wed Dec 07 14:56:35 CST 2016

Total time taken to generate the page: 0.07184 seconds