动态SQL

 create table #student(UserName varchar(20),Subject varchar(20),Score int)
insert into #student values('张三','语文',74)
insert into #student values('张三','数学',83)
insert into #student values('张三','物理',93)
insert into #student values('李四','语文',74)
insert into #student values('李四','数学',84)
insert into #student values('李四','物理',94)
/*
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
*/
declare @Sql varchar(1000)
set @sql='select b.UserName'
select @sql=@sql+' , sum(case when b.Subject= '''+Subject+''' then Score else 0 end)['+Subject+'] '
from (select distinct(Subject) from #student) b
set @sql=@sql+' from #student b group by b.UserName'
exec(@sql)

 

(2)

CREATE TABLE #TA(n1 NVARCHAR(2),n2 NVARCHAR(5),n3 NVARCHAR(2),n4 INT,n5 INT)
INSERT #TA
SELECT N'毛巾',N'哈尔滨',N'条',10,1 UNION ALL
SELECT N'水杯',N'哈尔滨',N'个',20,2

CREATE TABLE #TB(M1 NVARCHAR(5),M2 SMALLDATETIME,M3 INT,M4 INT)
INSERT #TB
SELECT N'一门店','20080101',20,1 UNION ALL
SELECT N'二门店','20080101',30,1 UNION ALL
SELECT N'一门店','20080101',40,2 UNION ALL
SELECT N'二门店','20080101',50,2 UNION ALL
SELECT N'一门店','20080102',60,1 UNION ALL
SELECT N'二门店','20080102',70,1 UNION ALL
SELECT N'一门店','20080102',80,2 UNION ALL
SELECT N'二门店','20080102',90,2

/*
想求出每个商品的现在有库存、每个门店的现有库存、总库存，如：
品名  产地    单位 数量  一门店  二门店  ……  总库存
毛巾  哈尔滨  条  10      60    70    …… 
水杯  哈尔滨  个  20      80    90    …… 
*/

declare @sql varchar(1000)
set @sql='select a.n1,a.n2,a.n3,a.n4'
select @sql=@sql+ ' ,sum(case when b.M1= '''+M1+''' then b.M3 else null end) ['+M1+']'
from (select distinct (M1) from #TB) a
set @sql=@Sql+ ' ,sum(M3) from #TA a,#TB b where a.n5=b.M4 group by a.n1,a.n2,a.n3,a.n4'
exec(@sql)