动态SQL(列转行)

本文介绍了一个使用SQL查询来统计不同班级在各科目中获得A、B、C等级的人数的方法。通过动态SQL和 unpivot 技术实现了灵活的科目数量处理。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

学分表如下
"语文,数学,外语"只是举例,希望代码希望能适应数目不定的科目
判定一个列是否科目的方法: 表中只要不是"班级"、"学籍号"、"姓名"的列,都是科目

班级 学籍号 姓名 语文 数学 外语
1 001 张 A A C
3 002 王 B A B
2 003 李 C A A
1 004 赵 C C B
3 005 周 A A A
2 006 吴 B B C
1 007 郑 C A A
想查询出以下结果:
班级 科目 人数A 人数B 人数C
1 语文 1 0 2
2 语文 0 1 1
3 语文 1 1 0
1 数学 2 0 1
2 数学 1 1 0
3 数学 2 0 0
1 外语 1 1 1
2 外语 1 0 1
3 外语 1 1 0

-----------------------------------------------------------

CREATE TABLE TB(CID INT, ID INT, NAME NVARCHAR(6), C1 VARCHAR(1), C2 VARCHAR(1), C3 VARCHAR(1))
INSERT TB
SELECT 1, '001', N'', 'A', 'A', 'C' UNION ALL
SELECT 3, '002', N'', 'B', 'A', 'B' UNION ALL
SELECT 2, '003', N'', 'C', 'A', 'A' UNION ALL
SELECT 1, '004', N'', 'C', 'C', 'B' UNION ALL
SELECT 3, '005', N'', 'A', 'A', 'A' UNION ALL
SELECT 2, '006', N'', 'B', 'B', 'C' UNION ALL
SELECT 1, '007', N'', 'C', 'A', 'A'

DECLARE @STR NVARCHAR(4000)
SET @STR=N''
SELECT @STR=@STR+N'UNION ALL SELECT CID,'''+NAME+N''' AS CS,'+NAME+N' FROM TB '
FROM syscolumns
WHERE ID=OBJECT_ID('TB') AND COLID>3
SET @STR=N'SELECT CID,CS,SUM(CASE WHEN C1=''A'' THEN 1 ELSE 0 END) AS A, '+
N
' SUM(CASE WHEN C1=''B'' THEN 1 ELSE 0 END) AS B, '+
N
' SUM(CASE WHEN C1=''C'' THEN 1 ELSE 0 END) AS C '+
N
' FROM ( '+
STUFF(@STR, 1, 10, N'')+
N
' ) T GROUP BY CS,CID ORDER BY CS,CID '
--PRINT @STR
EXEC(@STR)

DROP TABLE TB
/*
CID CS A B C
----------- ---- ----------- ----------- -----------
1 C1 1 0 2
2 C1 0 1 1
3 C1 1 1 0
1 C2 2 0 1
2 C2 1 1 0
3 C2 2 0 0
1 C3 1 1 1
2 C3 1 0 1
3 C3 1 1 0

(9 row(s) affected)

*/

------------------------------------------------------------

--蓉儿 szx1999

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([班级] int,[学籍号] varchar(3),[姓名] varchar(2),[语文] varchar(1),[数学] varchar(1),[外语] varchar(1))
insert [tb]
select 1,'001','','A','A','C' union all
select 3,'002','','B','A','B' union all
select 2,'003','','C','A','A' union all
select 1,'004','','C','C','B' union all
select 3,'005','','A','A','A' union all
select 2,'006','','B','B','C' union all
select 1,'007','','C','A','A'
go
--select * from [tb]

declare @sql nvarchar(4000)
select @sql=isnull(@sql+',','')+name from syscolumns
where id=object_id('tb') and name not in ('班级','学籍号','姓名')
set @sql='select 班级,科目
,[人数A]=sum(case 成绩 when
''A'' then 1 else 0 end)
,[人数B]=sum(case 成绩 when
''B'' then 1 else 0 end)
,[人数C]=sum(case 成绩 when
''C'' then 1 else 0 end)
from
(select * from tb
unpivot (成绩 for 科目 in(
'+@sql+')) b) t
group by 班级,科目
'
exec(@sql)
/*
班级 科目 人数A 人数B 人数C
----------- ----------- ----------- ----------- -----------
1 数学 2 0 1
2 数学 1 1 0
3 数学 2 0 0
1 外语 1 1 1
2 外语 1 0 1
3 外语 1 1 0
1 语文 1 0 2
2 语文 0 1 1
3 语文 1 1 0

(9 行受影响)
*/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值