select * from v_temp
上面的视图结果如下:
user_name     role_name
-------------------------
系统管理员        管理员         
feng                管理员         
feng                一般用户        
test                一般用户        


想把结果变成这样:
user_name       role_name
---------------------------
系统管理员        管理员         
feng                管理员,一般用户         
test                一般用户

解答：

create table a_test(name varchar(20),role2 varchar(20))
insert into a_test values('李','管理員')
insert into a_test values('張','管理員')
insert into a_test values('張','一般用戶')
insert into a_test values('常','一般用戶')

create function join_str(@content varchar(100))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+rtrim(role2) from a_test where [name]=@content
select @str=right(@str,len(@str)-1)
return @str
end
go

--调用：
select [name],dbo.join_str([name]) role2 from a_test group by [name]

--select distinct name,dbo.uf_test(name) from a_test

 

关于plsql的行列转换问题,

1 2 3 4
1 2 5 6
1 2 7 8

转换为
1 2 3 4 5 6 7 8
如果得到的結果是1行且是一列的話
SQL> with a as (select 1 id1,2 id2,3 id3,4 id4 from dual
2 union
3 select 1 id1,2 id2,5 id3,6 id4 from dual
4 union
5 select 1 id1,2 id2,7 id3,8 id4 from dual
6 )
7 select id1||','||id2||','||wmsys.wm_concat(id5) id6 from
8 (select id1,id2,id3||','||id4 id5 from a)
9 group by id1,id2
10 ;

ID6
--------------------------------------------------------------------------
1,2,3,4,5,6,7,8
如果是轉換成一行多列的話，就是這樣寫
SQL> with a as (select 1 id1,2 id2,3 id3,4 id4 from dual
2 union
3 select 1 id1,2 id2,5 id3,6 id4 from dual
4 union
5 select 1 id1,2 id2,7 id3,8 id4 from dual
6 )
7
7 select id1,id2,max(decode(rn,1,id3,null)) id3,
8 max(decode(rn,1,id4,null)) id4,
9 max(decode(rn,2,id3,null)) id5,
10 max(decode(rn,2,id4,null)) id6,
11 max(decode(rn,3,id3,null)) id7,
12 max(decode(rn,3,id4,null)) id8 from
13 (select id1,id2,id3,id4,row_number()over(partition by id1,id2 order by id1,id2,id3) rn from a)
14 group by id1,id2
15 order by id1,id2
16 /

ID1 ID2 ID3 ID4 ID5 ID6 ID7 ID8
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4 5 6 7 8

第二個行列轉換的例子
如下表：
ID       名称         类型         数量
1           0001         A             3
2         0001         A             1
3         0001         B             2
4         0002         A             4
5         0002         B             6
6         0002         B             3
查询的结果，我想要的是这种形式
名称       类型A       数量       类型B       数量
0001         A             4           B             2
0002         A             4           B             9

方法一
SQL> WITH A AS (SELECT 1 ID,'0001' NAME,'A' TYPE,3 QTY FROM DUAL
  2             UNION
  3             SELECT 2 ID,'0001' NAME,'A' TYPE,1 QTY FROM DUAL
  4             UNION
  5             SELECT 3 ID,'0001' NAME,'B' TYPE,2 QTY FROM DUAL
  6             UNION
  7             SELECT 4 ID,'0002' NAME,'A' TYPE,4 QTY FROM DUAL
  8             UNION
  9             SELECT 5 ID,'0002' NAME,'B' TYPE,6 QTY FROM DUAL
 10             UNION
 11             SELECT 6 ID,'0002' NAME,'B' TYPE,3 QTY FROM DUAL
 12             )
 13  SELECT NAME,'A',SUM(DECODE(TYPE,'A',QTY,0)) QTYA,'B',SUM(DECODE(TYPE,'B',QTY,0)) QTYB
 14  FROM A
 15  GROUP BY NAME
SQL> /
 
NAME 'A'       QTYA 'B'       QTYB
---- --- ---------- --- ----------
0001 A            4 B            2
0002 A            4 B            9
方法二
SQL> WITH A AS (SELECT 1 ID,'0001' NAME,'A' TYPE,3 QTY FROM DUAL
  2             UNION
  3             SELECT 2 ID,'0001' NAME,'A' TYPE,1 QTY FROM DUAL
  4             UNION
  5             SELECT 3 ID,'0001' NAME,'B' TYPE,2 QTY FROM DUAL
  6             UNION
  7             SELECT 4 ID,'0002' NAME,'A' TYPE,4 QTY FROM DUAL
  8             UNION
  9             SELECT 5 ID,'0002' NAME,'B' TYPE,6 QTY FROM DUAL
 10             UNION
 11             SELECT 6 ID,'0002' NAME,'B' TYPE,3 QTY FROM DUAL
 12             )
 13   SELECT NAME,MAX(DECODE(RN,1,TYPE,NULL)) TYPEA,
 14              MAX(DECODE(RN,1,QTY,NULL)) QTYA,
 15              MAX(DECODE(RN,2,TYPE,NULL)) TYPEB,
 16              MAX(DECODE(RN,2,QTY,NULL)) QTYB
 17  FROM (SELECT NAME,TYPE,QTY,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY NAME) RN FROM (select NAME,TYPE,SUM(QTY) QTY from A
 18  GROUP BY NAME,TYPE
 19  ORDER BY NAME))
 20  GROUP BY NAME
 21  /
 
NAME TYPEA       QTYA TYPEB       QTYB
---- ----- ---------- ----- ----------
0001 A              4 B              2
0002 A              4 B              9