难题:建视图,将一个表的列横向显示.

Question

有以下两个表:
表C:
ID:
PARENT_ID:
NAME:表P:
ID:表C的PARENT_ID对应表p的ID,表p与表c为一对多的关系,我想建一个视图,把表c中的name列横向显示,且视图中一个p表的记录只有一条数据.

Answer 1

--10g方法
SELECT PARENT_ID, WMSYS.WM_CONCAT(NAME)
  FROM C, P
 WHERE C.PARENT_ID = P.ID
 GROUP BY PARENT_ID;

Answer 2

SQL> select * from t1;                                    IDX
---------------------------------------
                                      1
                                      2
                                      3SQL> select * from t;                                    IDX CNAME
--------------------------------------- --------------------------------------------------------------------------------
                                      1 a
                                      1 b
                                      1 c
                                      2 a
                                      2 b
                                      3 c6 rows selectedSQL> 
SQL> SELECT t1.idx,
  2    MAX(decode(rn, 1, cname, NULL)) ||
  3    MAX(decode(rn, 2, ',' || cname, NULL)) ||
  4    MAX(decode(rn, 3, ',' || cname, NULL)) str
  5    FROM (SELECT idx,
  6    cname,
  7    row_number() over(PARTITION BY idx ORDER BY cname) AS rn FROM t
  8    ) t,t1 where t1.idx=t.idx
  9  GROUP BY t1.idx
 10  ORDER BY 1;                                    IDX STR
--------------------------------------- --------------------------------------------------------------------------------
                                      1 a,b,c
                                      2 a,b
                                      3 cSQL> 

Answer 3

c:
id  parent_id   name
1    2           a
2    2           b
3    2           c
4    3           a
5    3           b
5    3           cp:id 
2
3
我要建的视图:
id name1  name2 name3
2   a      b      c
3   a      b      c哪位高手知道,请帮忙解答一下,非常感谢

Answer 4

通用方法：SELECT PARENT_ID, MAX(NAME) names
  FROM (SELECT PARENT_ID, LTRIM(SYS_CONNECT_BY_PATH(NAME, ','), ',') NAME
          FROM (SELECT PARENT_ID,
                       NAME,
                       ROW_NUMBER() OVER(PARTITION BY PARENT_ID ORDER BY NAME) AS CURR,
                       ROW_NUMBER() OVER(PARTITION BY PARENT_ID ORDER BY NAME) - 1 AS PREV
                  FROM C, P
                 WHERE C.PARENT_ID = P.ID)
         START WITH CURR = 1
        CONNECT BY PREV = PRIOR CURR
               AND PARENT_ID = PRIOR PARENT_ID)
 GROUP BY PARENT_ID
 ORDER BY 1;

Answer 5

如果是固定的列就用下面的方法好了，如果是不固定的列就要写存储过程动态拼SQL的方式了：
SQL> SELECT CC.PARENT_ID,
  2         MAX(DECODE(RN,1,NAME,NULL)) "NAME1",
  3         MAX(DECODE(RN,2,NAME,NULL)) "NAME2",
  4         MAX(DECODE(RN,3,NAME,NULL)) "NAME3"
  5    FROM (
  6          SELECT C.*,
  7                 ROW_NUMBER() OVER(PARTITION BY PARENT_ID ORDER BY ID) "RN"
  8            FROM C
  9         )CC,
 10         P
 11   WHERE CC.PARENT_ID = P.ID
 12   GROUP BY CC.PARENT_ID; PARENT_ID NAME1 NAME2 NAME3
---------- ----- ----- -----
         2 A     B     C
         3 A     B     C
SQL> SELECT C.PARENT_ID,
  2         WMSYS.WM_CONCAT(C.NAME) "NAME"
  3    FROM C,
  4         P
  5   WHERE C.PARENT_ID = P.ID
  6   GROUP BY C.PARENT_ID; PARENT_ID NAME
---------- --------------------------------------------------------------------------------
         2 A,B,C
         3 A,B,CSQL>