哪位大神帮看看这个sql怎么写

[失足成万古风流人物]

1 1
1 2
1 3
......
2 1
2 2
2 3
......
3 1
3 2
3 3
......
转成如下格式：
1 1 2 3......
2 1 2 3......
3 1 2 3......

[stelf]

with tmp as ( select 1 as p_id, 1 as p_num from dual union all select 1, 2 from dual union all select 1, 3 from dual union all select 2, 1 from dual union all select 2, 2 from dual union all select 2, 3 from dual union all select 3, 1 from dual union all select 3, 2 from dual union all select 3, 3 from dual) select p_id, listagg(p_num, ',') within group(order by 1) as p_num from tmp group by p_id

[stelf]

如果不拼接，就用group by + case when 来凑多列

[失足成万古风流人物]

受教了，oracle也存在pivot函数。

引用 1 楼 听雨停了 的回复:

use Tempdb
go
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([colA] int,[colB] int)
Insert #tab
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 3,1 union all
select 3,2 union all
select 3,3 UNION ALL
SELECT 3,4
Go
SELECT * FROM #tab;
--测试数据结束


DECLARE @name VARCHAR(max),@sql VARCHAR(max)
SET @name =stuff((SELECT DISTINCT ',[Col'+convert(varchar,rn)+']'  FROM 
(Select *,ROW_NUMBER() OVER(PARTITION BY colA ORDER BY colB) AS rn from #tab)a 
FOR XML PATH('')),1,1,'');
set @sql ='SELECT * from (Select *, ''Col'' +convert(varchar,ROW_NUMBER() OVER(PARTITION BY colA ORDER BY colB)) AS rn from #tab)a pivot(max(colB)for rn in('+@name+'))a'
EXEC( @sql)


colA        colB
----------- -----------
1           1
1           2
1           3
2           1
2           2
2           3
3           1
3           2
3           3
3           4

(10 行受影响)

colA        Col1        Col2        Col3        Col4
----------- ----------- ----------- ----------- -----------
1           1           2           3           NULL
2           1           2           3           NULL
3           1           2           3           4

(3 行受影响)

[听雨停了]

use Tempdb
go
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([colA] int,[colB] int)
Insert #tab
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 3,1 union all
select 3,2 union all
select 3,3 UNION ALL
SELECT 3,4
Go
SELECT * FROM #tab;
--测试数据结束


DECLARE @name VARCHAR(max),@sql VARCHAR(max)
SET @name =stuff((SELECT DISTINCT ',[Col'+convert(varchar,rn)+']'  FROM 
(Select *,ROW_NUMBER() OVER(PARTITION BY colA ORDER BY colB) AS rn from #tab)a 
FOR XML PATH('')),1,1,'');
set @sql ='SELECT * from (Select *, ''Col'' +convert(varchar,ROW_NUMBER() OVER(PARTITION BY colA ORDER BY colB)) AS rn from #tab)a pivot(max(colB)for rn in('+@name+'))a'
EXEC( @sql)


colA        colB
----------- -----------
1           1
1           2
1           3
2           1
2           2
2           3
3           1
3           2
3           3
3           4

(10 行受影响)

colA        Col1        Col2        Col3        Col4
----------- ----------- ----------- ----------- -----------
1           1           2           3           NULL
2           1           2           3           NULL
3           1           2           3           4

(3 行受影响)

[听雨停了]

忘了这事Oracle版了，上面的是Sql server的写法。Oracle你可以用Max(Case when 。。。)这种写法也可以实现