--构造演示环境
CREATE TABLE #TMP_Source
(
AutoID INT IDENTITY(1, 1),
Type CHAR(1),
Value INT
)INSERT INTO #TMP_Source (Type, Value)
SELECT 'A', 1
UNION ALL SELECT 'A', 2
UNION ALL SELECT 'B', 3
UNION ALL SELECT 'B', 4
UNION ALL SELECT 'B', 6
UNION ALL SELECT 'C', 7
UNION ALL SELECT 'C', 89
UNION ALL SELECT 'D', 9
UNION ALL SELECT 'D', 1
UNION ALL SELECT 'D', 234
UNION ALL SELECT 'D', 24--分类别求和,并横向显示
SELECT SUM(
CASE Type
WHEN 'A' THEN Value
ELSE 0
END
) AS SumA,
SUM(
CASE Type
WHEN 'B' THEN Value
ELSE 0
END
) AS SumB,
SUM(
CASE Type
WHEN 'C' THEN Value
ELSE 0
END
) AS SumC,
SUM(
CASE Type
WHEN 'D' THEN Value
ELSE 0
END
) AS SumD
FROM #TMP_SourceDROP TABLE #TMP_Source--对于列不定的,就要动态拼sql了,原理相似
CREATE TABLE #TMP_Source
(
AutoID INT IDENTITY(1, 1),
Type CHAR(1),
Value INT
)INSERT INTO #TMP_Source (Type, Value)
SELECT 'A', 1
UNION ALL SELECT 'A', 2
UNION ALL SELECT 'B', 3
UNION ALL SELECT 'B', 4
UNION ALL SELECT 'B', 6
UNION ALL SELECT 'C', 7
UNION ALL SELECT 'C', 89
UNION ALL SELECT 'D', 9
UNION ALL SELECT 'D', 1
UNION ALL SELECT 'D', 234
UNION ALL SELECT 'D', 24--分类别求和,并横向显示
SELECT SUM(
CASE Type
WHEN 'A' THEN Value
ELSE 0
END
) AS SumA,
SUM(
CASE Type
WHEN 'B' THEN Value
ELSE 0
END
) AS SumB,
SUM(
CASE Type
WHEN 'C' THEN Value
ELSE 0
END
) AS SumC,
SUM(
CASE Type
WHEN 'D' THEN Value
ELSE 0
END
) AS SumD
FROM #TMP_SourceDROP TABLE #TMP_Source--对于列不定的,就要动态拼sql了,原理相似
Create table test (BanJ char(10),name char(10),km char(10),cj int)
go
insert test values('一班','张三','语文',80)
insert test values('一班','张三','数学',86)
insert test values('一班','张三','英语',75)
insert test values('一班','王五','语文',60)
insert test values('一班','王五','数学',80)
insert test values('一班','王五','英语',70)
insert test values('二班','李四','语文',70)
insert test values('二班','李四','数学',85)
insert test values('二班','李四','英语',78)
insert test values('二班','小吴','语文',70)
insert test values('二班','小吴','数学',80)
insert test values('二班','小吴','英语',70)
想得到如下数据格式,通过动态SQL语句怎么实现:班级 姓名 语文 数学 英语
一班 张三 80 86 75
一班 王五 60 80 70
一班 小计 140 146 145
二班 李四 78 85 78
二班 小吴 70 80 70
二班 小计 148 165 148----------------------------------
declare @str varchar(8000)
set @str=''
select @str=@str+'['+rtrim(km)+']=max(case km when '''+rtrim(km)+''' then cj else 0 end),' from test group by km
set @str =left(@str,len(@str)-1)
print @str
exec ('select BanJ,name,'+@str+' from test group by BanJ,name order by BanJ desc')
aa 20 1
bb 30 1
cc 40 1
aa 20 1
bb 30 1
cc 40 1declare @s1 varchar(8000),@s2 varchar(8000)
select @s1='',@s2=''
select @s1=@s1+','''+DepartmentName+''''
,@s2=@s2+',['+DepartmentName+']=isnull( cast('+cast(PersonNum as varchar)+' as varchar),'''')'
from Department
exec('
select ''部门名称'''+@s1+'
union
select ''部门人数'''+@s2+' from Department')
————————————————————————————————————————
结果:
部门名称 aa bb cc aa bb cc
部门人数 20 30 40 20 30 40