DECLARE @sql varchar(8000)
DECLARE @city varchar(30)DECLARE cr CURSOR FOR SELECT da FROM city GROUP BY daOPEN cr
FETCH NEXT FROM cr INTO @cityset @sql='select a.type,a.fx from city a '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql=@sql+',inner join (select type,fx, min(day) as '+@city+' from city where da='+@city+
') as '+@city+' on a.type='+@city+'.type and a.fx='+@city+'.fx'
FETCH NEXT FROM cr INTO @city
END
set @sql=@sql+' group by a.type,a.fx'
exec(@sql)
CLOSE cr
DEALLOCATE cr
declare @Sql varchar(5000)select @FieldsCreate='',@FieldsSelect=''select @FieldsSelect=@FieldsSelect+',max(case when da='''+rtrim(da)+''' then day else null) as '''+rtrim(da)+'''' from #Tempfrop table #Tempselect @Sql='select type,fx,'+@FieldsSelect+' from table1 group by type,fx'
exec (@sql)自己改改可以变成一个存储过程.
declare @Sql varchar(5000)select @FieldsCreate='',@FieldsSelect=''select @FieldsSelect=@FieldsSelect+',max(case when da='''+rtrim(da)+''' then day else null) as '''+rtrim(da)+'''' from #Tempfrop table #Tempselect @Sql='select type,fx,'+@FieldsSelect+' from table1 group by type,fx'
exec (@sql)自己改改可以变成一个存储过程.
SELECT Da
FROM table
ORDER BY Dasql = "select type,fx"OPEN cur_city
FETCH NEXT FROM cur_city
INTO @city
WHILE @@FETCH_STATUS = 0
BEGIN
sql = sql + ",case da when " + @city + " day else 0" FETCH NEXT FROM cur_city
INTO @city
ENDsql = sql + " from (select type,fx,Da,sum(day) as day from table group by type,fx,Da) a order by type,fx"
CLOSE cur_city
DEALLOCATE cur_city
SELECT DISTINCT Da
FROM table
ORDER BY Dadeclare @Sql varchar(5000)BEGIN
SET @sql = "select type,fx" OPEN cur_city
FETCH NEXT FROM cur_city
INTO @city
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ",case da when " + @city + " day else 0 " FETCH NEXT FROM cur_city
INTO @city
END @sql = @sql + " from (select type,fx,Da,sum(day) as day from table group by type,fx,Da) a order by type,fx"
CLOSE cur_city
DEALLOCATE cur_city exec (@sql)
END
SELECT DISTINCT Da
FROM table
ORDER BY Dadeclare @Sql varchar(5000)BEGIN
SET @sql = "select type,fx" OPEN cur_city
FETCH NEXT FROM cur_city
INTO @city
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ",case da when " + @city + " day else 0 " FETCH NEXT FROM cur_city
INTO @city
END @sql = @sql + " from (select type,fx,Da,sum(day) as day from table group by type,fx,Da) a order by type,fx"
CLOSE cur_city
DEALLOCATE cur_city exec (@sql)
END
declare @Sql varchar(5000)select @FieldsSelect=''select @FieldsSelect=@FieldsSelect+',max(case when da='''+rtrim(da)+''' then day else null end) as '''+rtrim(da)+'''' from #Tempfrop table #Tempselect @Sql='select type,fx'+@FieldsSelect+' from table1 group by type,fx'
exec (@sql)
DECLARE @s1 varchar(300)
DECLARE @city varchar(30)DECLARE cr CURSOR FOR SELECT da FROM city GROUP BY daOPEN cr
FETCH NEXT FROM cr INTO @cityset @sql=''
set @s1=''
WHILE @@FETCH_STATUS = 0
BEGIN
set @s1=@s1+',replace(isnull(max('+@city+'.'+@city+'),0),''0'','''') '+@city
SET @sql=@sql+' left join (select id,type,fx, [day] as '+@city+' from city where da='''+@city+
''') '+@city+' on a.id='+@city+'.id'
FETCH NEXT FROM cr INTO @city
END
set @sql=+'select a.type,a.fx'+@s1+' from city a'+@sql+' group by a.type,a.fx'
exec(@sql)
CLOSE cr
DEALLOCATE cr
GO--- test ok,above has error
DECLARE @s1 varchar(300)
DECLARE @city varchar(30)DECLARE cr CURSOR FOR SELECT da FROM city GROUP BY daOPEN cr
FETCH NEXT FROM cr INTO @cityset @sql=''
set @s1=''
WHILE @@FETCH_STATUS = 0
BEGIN
set @s1=@s1+',replace(isnull(max('+@city+'.'+@city+'),0),''0'','''') '+@city
SET @sql=@sql+' left join (select id,type,fx, [day] as '+@city+' from city where da='''+@city+
''') '+@city+' on a.id='+@city+'.id'
FETCH NEXT FROM cr INTO @city
END
set @sql=+'select a.type,a.fx'+@s1+' from city a'+@sql+' group by a.type,a.fx'
exec(@sql)
CLOSE cr
DEALLOCATE cr
GO--- test ok,above has error
DECLARE @s1 varchar(300)
DECLARE @city varchar(30)DECLARE cr CURSOR FOR SELECT da FROM city GROUP BY daOPEN cr
FETCH NEXT FROM cr INTO @cityset @sql=''
set @s1=''
WHILE @@FETCH_STATUS = 0
BEGIN
set @s1=@s1+',replace(isnull(max('+@city+'.'+@city+'),0),''0'','''') '+@city
SET @sql=@sql+' left join (select id,type,fx, [day] as '+@city+' from city where da='''+@city+
''') '+@city+' on a.id='+@city+'.id'
FETCH NEXT FROM cr INTO @city
END
set @sql=+'select a.type,a.fx'+@s1+' from city a'+@sql+' group by a.type,a.fx'
exec(@sql)
CLOSE cr
DEALLOCATE cr
GO--- test ok,above has error
select distinct da into #Temp from test2declare @FieldsSelect varchar(1000)
declare @Sql varchar(5000)select @FieldsSelect=''select @FieldsSelect=@FieldsSelect+',max(case when da='''+rtrim(da)+''' then day else null end) as '''+rtrim(da)+'''' from #Tempdrop table #Tempselect @Sql='select type,fx'+@FieldsSelect+' from test2 group by type,fx'--print @sql
exec (@sql)select * from test2result:
type fx 北京 广州 南京 上海 天津
---------- ---------- ----------- ----------- ----------- ----------- -----------
长 上 NULL 3 NULL 5 NULL
短 上 4 NULL NULL 6 6
长 下 3 4 NULL NULL NULL
短 下 NULL NULL 4 NULL NULL警告: 聚合或其它 SET 操作消除了空值。
id type fx da day
----------- ---------- ---------- ---------- -----------
1 长 上 广州 3
2 长 上 上海 5
3 短 上 北京 4
4 短 上 天津 6
5 长 下 广州 4
6 长 下 北京 3
7 短 上 上海 6
8 短 下 南京 4
我的环境:
WIN2000+SQL SERVER 2000中文企业版SP2
你的呢?
warning under the case you set query as texttoday is the bad net that I can't send out test result.