create table a(MC char(2),VALUE int)
insert into a select 'aa',12
insert into a select 'bb',20
insert into a select 'cc',36
insert into a select 'dd',11declare @s varchar(8000)
set @s = ''
select @s = @s + ','+MC+'=max(case when MC = '''+MC+''' then VALUE end)'
from a group by mc
set @s = 'select '+ stuff(@s,1,1,'')+' from a'
exec(@s)
insert into a select 'aa',12
insert into a select 'bb',20
insert into a select 'cc',36
insert into a select 'dd',11declare @s varchar(8000)
set @s = ''
select @s = @s + ','+MC+'=max(case when MC = '''+MC+''' then VALUE end)'
from a group by mc
set @s = 'select '+ stuff(@s,1,1,'')+' from a'
exec(@s)
Item char(1),
Type char(2),
Qty int,
PKG char(2))insert into t select 'A','T1',2 ,'P1'
insert into t select 'A','T2',5 ,'P1'
insert into t select 'B','T2',1 ,'P1'
insert into t select 'B','T3',4 ,'P1'
insert into t select 'C','T1',10,'P2'
insert into t select 'D','T4',2 ,'P2'
insert into t select 'E','' ,0 ,'P2'
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL= @SQL+ ','+quotename(case when Type='' then ' ' else Type end)
+'=isnull(sum(CASE Type when '+quotename(type,'''')+' THEN Qty END),0)'
FROM T A where Rtrim(Type) != '' and Type is not null GROUP BY Typeset @SQL = 'select case when PKG is null and Item is null then ''TOTAL''
when PKG is not null and Item is null then ''SUM''
else PKG
end,
Item'+@SQL+',TOTAL=sum(Qty) from T group by PKG,Item with rollup'exec(@SQL)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)想变成姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
GO
Insert A Values('aa', 12)
Insert A Values('bb', 20)
Insert A Values('cc', 36)
Insert A Values('dd', 11)
GO
declare @s1 nvarchar(4000)
select @s1=''
select @s1=@s1+','+[MC]+'=(Select [VALUE] from A Where [MC]='''+[MC]+''') ' from A group by [MC]
Set @s1='select Distinct ' + STUFF(@s1,1,1,'') + ' from A '
exec(@s1)
GO
Drop table A
GO
create table a(MC char(2),VALUE int)
insert into a select 'aa',12
insert into a select 'bb',20
insert into a select 'cc',36
insert into a select 'dd',11
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL= @SQL+
',max(case when mc = ''' + t + ''' THEN value else 0 END)[' + t + ']'
FROM (SELECT DISTINCT mc as t FROM a) A
SET @SQL='select '+stuff(@SQL,1,1,'')+' FROM a'
exec (@SQL)
--删除测试环境
Drop Table a