34,590
社区成员
发帖
与我相关
我的任务
分享
select sname,score,@rank:=case when @sname=sname then @rank+1 when @sname:=sname then 1 else 1 end as c from rows ,(select @rank:=0,@sname='') b;
运行结果如下图:
select sname,
sum(case when c=1 then score end) as f1,
sum(case when c=2 then score end) as f2,
sum(case when c=3 then score end) as f3,
sum(case when c=4 then score end) as f4,
sum(case when c=5 then score end) as f5,
sum(case when c=6 then score end) as f6,
sum(case when c=7 then score end) as f7,
sum(case when c=8 then score end) as f8,
sum(case when c=9 then score end) as f9,
sum(case when c=10 then score end) as f10
from (select sname,score,@rank:=case when @sname=sname then @rank+1 when @sname:=sname then 1 else 1 end as c from rows ,(select @rank:=0,@sname='') b) new
group by sname;
运行结果是:
DECLARE @Cols VARCHAR(3),@Sql NVARCHAR(4000)='';
SELECT TOP 1 @Cols=COUNT(*) from #T1 GROUP BY 姓名 ORDER BY COUNT(*) DESC
PRINT @Cols
WHILE @Cols>0 --栏位数递减,生成循环
begin
SELECT @Sql=',MAX(CASE WHEN RN='+@Cols+' THEN RTRIM([分数]) ELSE '''' END) AS 分数'+@Cols+@Sql,@Cols-=1;--递减判断@Col
PRINT @Sql
PRINT @Cols
END
SET @Sql='SELECT [姓名]'+ @Sql+' FROM (SELECT *,RN=ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY 姓名) from #T1) AS T group by [姓名] ORDER BY [姓名]'
PRINT @Cols
EXEC(@Sql)
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([姓名] nvarchar(102),[分数] bigint)
Insert #T1
select N'张三',80 union all
select N'张三',90 union all
select N'张三',100 union all
select N'李四',50 union all
select N'李四',60 union all
select N'王五',90 union all
select N'赵六',40 union all
select N'赵六',50 union all
select N'赵六',60 union all
select N'赵六',70 union all
select N'赵六',80
GO
DECLARE @Cols VARCHAR(3),@Sql NVARCHAR(4000)='';
SELECT TOP 1 @Cols=COUNT(*) from #T1 GROUP BY 姓名 ORDER BY COUNT(*) desc
WHILE @Cols>0
SELECT @Sql=',MAX(CASE WHEN RN='+@Cols+' THEN RTRIM([分数]) ELSE '''' END) AS 分数'+@Cols+@Sql,@Cols-=1;
SET @Sql='SELECT [姓名]'+ @Sql+' FROM (SELECT *,RN=ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY 姓名) from #T1) AS T group by [姓名] ORDER BY [姓名]'
EXEC(@Sql)
/*
姓名 分数1 分数2 分数3 分数4 分数5
李四 50 60
王五 90
张三 80 90 100
赵六 40 50 60 70 80*/
https://bbs.csdn.net/topics/392397857