sql行转列语句
代码
--delete from #tba
--delete from #tbb
create table #tba ( sid int ,n varchar(20) )
create table #tbb (sid int ,c varchar(20) ,s float )
insert #tba
select 1,'z1' union all
select 2,'z2' union all
select 3,'z3' union all
select 4,'z4' union all
select 5,'z5'
insert #tbb
select 1, 'shuxue',50 union all
select 1, 'english', 60 union all
select 1, 'yuwen', 90 union all
select 2, 'shuxue',70 union all
select 2, 'english', 40 union all
select 2, 'yuwen', 35 union all
select 3, 'shuxue',57 union all
select 3, 'english', 64 union all
select 3, 'yuwen', 93 union all
select 8, 'shuxue',55 union all
select 8, 'english', 84 union all
select 8, 'yuwen', 90 union all
select 4, 'english', 65 union all
select 4, 'yuwen', 33
--select * from @tba
--select * from #tbb
--固定字段方式行转列
/*
select sid,sum(case when c = 'shuxue' then s else 0 end) as 'shuxue',
sum(case when c = 'english' then s else 0 end) as 'english',
sum(case when c = 'yuwen' then s else 0 end) as 'yuwen'
from @tbb
group by sid
*/
--动态方式行转列
declare @sql varchar(8000)
set @sql = 'select sid,'
select @sql = @sql + 'sum(case when c = '''+c+''' then s else null end) as '''+c+''','
from (select distinct c from #tbb) as B
set @sql = left(@sql,len(@sql)-1) + ' from #tbb group by sid'
--PRINT @SQL
exec(@sql)
/*
*/
--回顾下左外连接,右外连接,全外连接;交叉连接(笛卡尔乘积),内连接
--select * from @tba a left join @tbb b on a.sid = b.sid
--select * from @tba a right join @tbb b on a.sid = b.sid
--select * from @tba a full join @tbb b on a.sid = b.sid
--select * from @tba cross join @tbb
--select * from @tba a inner join @tbb b on a.sid = b.sid
--select * from @tba a join @tbb b on a.sid = b.sid
--select * from @tba a, @tbb b where a.sid = b.sid
--having关键字
/*
select a.sid,count(a.sid) cid from @tba a left join @tbb b on a.sid = b.sid
group by a.sid having count(a.sid) > 2
--select count(sid) as cid from @tbb group by sid having count(sid)>2
*/
--delete from #tba
--delete from #tbb
create table #tba ( sid int ,n varchar(20) )
create table #tbb (sid int ,c varchar(20) ,s float )
insert #tba
select 1,'z1' union all
select 2,'z2' union all
select 3,'z3' union all
select 4,'z4' union all
select 5,'z5'
insert #tbb
select 1, 'shuxue',50 union all
select 1, 'english', 60 union all
select 1, 'yuwen', 90 union all
select 2, 'shuxue',70 union all
select 2, 'english', 40 union all
select 2, 'yuwen', 35 union all
select 3, 'shuxue',57 union all
select 3, 'english', 64 union all
select 3, 'yuwen', 93 union all
select 8, 'shuxue',55 union all
select 8, 'english', 84 union all
select 8, 'yuwen', 90 union all
select 4, 'english', 65 union all
select 4, 'yuwen', 33
--select * from @tba
--select * from #tbb
--固定字段方式行转列
/*
select sid,sum(case when c = 'shuxue' then s else 0 end) as 'shuxue',
sum(case when c = 'english' then s else 0 end) as 'english',
sum(case when c = 'yuwen' then s else 0 end) as 'yuwen'
from @tbb
group by sid
*/
--动态方式行转列
declare @sql varchar(8000)
set @sql = 'select sid,'
select @sql = @sql + 'sum(case when c = '''+c+''' then s else null end) as '''+c+''','
from (select distinct c from #tbb) as B
set @sql = left(@sql,len(@sql)-1) + ' from #tbb group by sid'
--PRINT @SQL
exec(@sql)
/*
*/
--回顾下左外连接,右外连接,全外连接;交叉连接(笛卡尔乘积),内连接
--select * from @tba a left join @tbb b on a.sid = b.sid
--select * from @tba a right join @tbb b on a.sid = b.sid
--select * from @tba a full join @tbb b on a.sid = b.sid
--select * from @tba cross join @tbb
--select * from @tba a inner join @tbb b on a.sid = b.sid
--select * from @tba a join @tbb b on a.sid = b.sid
--select * from @tba a, @tbb b where a.sid = b.sid
--having关键字
/*
select a.sid,count(a.sid) cid from @tba a left join @tbb b on a.sid = b.sid
group by a.sid having count(a.sid) > 2
--select count(sid) as cid from @tbb group by sid having count(sid)>2
*/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】博客园携手 AI 驱动开发工具商 Chat2DB 推出联合终身会员
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步