Convert to rows by comma【By hmxxyy】

SQL> select c2 from test;

C2
----------------------------------------
a,b,c,dd,efg
h,iii

select decode(lvl, 1, substr(c2, 1, instr(c2, ',', 1) - 1),
length(c2) - length(replace(c2, ',')) + 1, substr(c2, instr(c2, ',', 1, lvl-1)+1),
substr(c2, instr(c2, ',', 1, lvl-1) + 1, instr(c2, ',', 1, lvl) - instr(c2, ',', 1, lvl-1) -1))
what_you_need
from
( select level lvl
from (select data_length from user_tab_columns where table_name = 'TEST'
and column_name = 'C2')
connect by 1=1
and level <= data_length
),
test
where lvl < length(c2) - length(replace(c2, ',')) + 2
/

WHAT_YOU_NEED
-------------
a
b
c
dd
efg
h
iii

Written on 2005/03/13

hmxxyy的第二个语句的执行计划
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'TEST'
   3    1     VIEW
   4    3       CONNECT BY (WITHOUT FILTERING)
   5    4         COUNT
   6    5           VIEW
   7    6             SORT (AGGREGATE)
   8    7               TABLE ACCESS (FULL) OF 'TEST'
 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
       1160  bytes sent via SQL*Net to client
        536  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         25  rows processed
 
偶的语句的执行计划
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     SORT (UNIQUE)
   3    2       CONNECT BY (WITH FILTERING)
   4    3         NESTED LOOPS
   5    4           TABLE ACCESS (FULL) OF 'TEST'
   6    4           TABLE ACCESS (BY USER ROWID) OF 'TEST'
   7    3         NESTED LOOPS
   8    7           BUFFER (SORT)
   9    8             CONNECT BY PUMP
  10    7           TABLE ACCESS (FULL) OF 'TEST'
 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       1161  bytes sent via SQL*Net to client
        536  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         25  rows processed

请问SQL 2000中如何实现行列的转化？
另，我在论坛里询问机动车驾驶员管理软件的介绍和架构说明您有没有？谢谢

我已经回复ITPUB里的那个帖子。

谢谢回复。
行列转换和SQL里面的旋转数据表是不是一个概念？
前几天参加了金碟和海波龙共同开的管理绩效软件会议，对其中可以任意将行旋转变成列，列旋转变成行的功能印象非常深刻，简直和CORELDROW一样，呵呵，管理软件也可以这样做，确实震惊。这个不知道和你说的行列转换一样吗？这个功能实现起来复杂吗？

呵呵，旋转数据表是什么东东？没听说过的说

transform
pivot table
就是把行数据转换到列上啊
select case a when a>b then c else0 end as q from table1

汗，google都没搜到：）
不过这个名词pivot table倒是见过
你说的时不时跟excel的粘贴里的转置功能一样？
这里所说的行列转换跟那个差不多，不过还是有一些区别
这里的不是全部转，只是部分行转换为列

打开MS SQL2000联机帮助,搜索"旋转数据表".上次的写错了,应该是:
select sum(case a when b>0 then c else 0 end)as f from tableA where .........

嗯，跟这个sql表达的含义差不多