declare @name varchar(24) declare @total varchar(1024) DECLARE authors_cursor CURSOR FOR SELECT au_lname FROM authors --WHERE au_lname LIKE 'B%' ORDER BY au_lnameOPEN authors_cursor-- Perform the first fetch. FETCH NEXT FROM authors_cursor into @name set @total=@name -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM authors_cursor into @name set @total=@total +' ' + @name END print @total CLOSE authors_cursor DEALLOCATE authors_cursor
应该是行转列的问题吧 给你一个例子Create table newtest (name char(10),Subject char(10),Result int) go insert newtest values('张三','语文',80) insert newtest values('张三','数学',86) insert newtest values('张三','英语',75) insert newtest values('李四','语文',78) insert newtest values('李四','数学',85) insert newtest values('李四','英语',78) declare @sql nvarchar(4000) set @sql = 'select name' select @sql = @sql + ' , sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']' from (select distinct Subject from newtest) as a set @sql = @sql + ' from newtest group by name' exec(@sql)
create table tb(id int,name varchar(10)) insert into tb select 1,'a001' union all select 2,'a002' union all select 3,'a003' union all select 4,'a004' union all select 5,'a005' union all select 6,'a006' union all select 7,'a007' union all select 8,'a008'--静态 select col1=max(case id when 1 then 'a001' else '' end), col2=max(case id when 2 then 'a002' else '' end), col3=max(case id when 3 then 'a003' else '' end), col4=max(case id when 4 then 'a004' else '' end), col5=max(case id when 5 then 'a005' else '' end), col6=max(case id when 6 then 'a006' else '' end), col7=max(case id when 7 then 'a007' else '' end), col8=max(case id when 8 then 'a008' else '' end) from tb--动态 declare @sql varchar(8000) set @sql='select ' select @sql=@sql+',[col'+cast(id as varchar)+']=max(case id when '''+cast(id as varchar)+''' then name else '''' end)' from tb group by id set @sql=stuff(@sql,8,1,'') exec(@sql+' from tb')drop table tb
declare @total varchar(1024)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
--WHERE au_lname LIKE 'B%'
ORDER BY au_lnameOPEN authors_cursor-- Perform the first fetch.
FETCH NEXT FROM authors_cursor into @name
set @total=@name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor into @name
set @total=@total +' ' + @name
END
print @total
CLOSE authors_cursor
DEALLOCATE authors_cursor
给你一个例子Create table newtest (name char(10),Subject char(10),Result int)
go
insert newtest values('张三','语文',80)
insert newtest values('张三','数学',86)
insert newtest values('张三','英语',75)
insert newtest values('李四','语文',78)
insert newtest values('李四','数学',85)
insert newtest values('李四','英语',78)
declare @sql nvarchar(4000)
set @sql = 'select name'
select @sql = @sql + ' , sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from newtest) as a
set @sql = @sql + ' from newtest group by name'
exec(@sql)
insert into tb select 1,'a001'
union all select 2,'a002'
union all select 3,'a003'
union all select 4,'a004'
union all select 5,'a005'
union all select 6,'a006'
union all select 7,'a007'
union all select 8,'a008'--静态
select col1=max(case id when 1 then 'a001' else '' end),
col2=max(case id when 2 then 'a002' else '' end),
col3=max(case id when 3 then 'a003' else '' end),
col4=max(case id when 4 then 'a004' else '' end),
col5=max(case id when 5 then 'a005' else '' end),
col6=max(case id when 6 then 'a006' else '' end),
col7=max(case id when 7 then 'a007' else '' end),
col8=max(case id when 8 then 'a008' else '' end)
from tb--动态
declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+',[col'+cast(id as varchar)+']=max(case id when '''+cast(id as varchar)+''' then name else '''' end)' from tb group by id
set @sql=stuff(@sql,8,1,'')
exec(@sql+' from tb')drop table tb