例如下图:a组可能是3行,b组2行,c组5行
declare @t table
(
id int, groups varchar(max), Name varchar(max)
);
insert into @t
select 1,'a','分类a1' union all
select 2,'a','分类a2' union all
select 3,'a','分类a3' union all
select 4,'b','分类b1' union all
select 5,'b','分类b2' union all
select 6,'c','分类c1' union all
select 7,'c','分类c2' union all
select 8,'c','分类c3' union all
select 9,'c','分类c4' union all
select 10,'c','分类c5'
直接用PIVOT函数进行行列转换
SELECT * FROM(SELECT * from @t)t
PIVOT(MAX(Name) FOR groups IN(a,b,c))pvt
不是自己想要结果
使用ROW_NUMBER PARTITION 得到分组 序号
SELECT ROW_NUMBER() over(PARTITION by groups order by id) rn,groups,Name from @t
然后再使用 PIVOT转换即可
SELECT * FROM(SELECT ROW_NUMBER() over(PARTITION by groups order by id) rn,groups,Name from @t)t
PIVOT(MAX(Name) FOR groups IN(a,b,c))pvt