Sql Sever¼Ç¼µÄÐÐÁÐת»»

ÒÑÖªÒ»¸ö±íµÄ½á¹¹Îª£º
ÐÕÃû ¿ÆÄ¿ ³É¼¨
ÕÅÈý ÓïÎÄ 20
ÕÅÈý Êýѧ 30
ÕÅÈý Ó¢Óï 50
ÀîËÄ ÓïÎÄ 70
ÀîËÄ Êýѧ 60
ÀîËÄ Ó¢Óï 90
ÔõÑùͨ¹ýselectÓï¾ä°ÑËû±ä³ÉÒÔϽṹ£º
ÐÕÃû ÓïÎÄ Êýѧ Ó¢Óï
ÕÅÈý 20 30 50
ÀîËÄ 70 60 90
´ð:
CREATE TABLE [dbo].[Stu] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Class] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[score] [int] NOT NULL
) ON [PRIMARY]
GO
select * from Stu
insert into Stu values('ÂíÀï','ÓïÎÄ',80)
insert into Stu values('ÂíÀï','Êýѧ',96)
insert into Stu values('¸¶Ë§','ÓïÎÄ',85)
insert into Stu values('¸¶Ë§','Êýѧ',85)
insert into Stu values('ÌÆ×ÓÒâ','ÓïÎÄ',30)
insert into Stu values('ÌÆ×ÓÒâ','Êýѧ',50)
insert into Stu values('Íõº£³½','Ó¢Óï',35)
declare @sql nvarchar(4000)
set @sql=''
select @sql = @sql + ',sum(case class when '''+ class + ''' then score else 0 end) as ' +class from stu group by class
set @sql = 'select name '+@sql+' from stu group by name'
print @sql
exec(@sql)
(
¿ÉÒÔÊÔÊÔÕâ¸ö:
declare @sql nvarchar(4000)
set @sql=''
select @sql = @sql + class from stu
print @sql
)
ÁíÒ»ÖÖ:
--´´½¨´æ´¢¹ý³Ì
create proc dbo.usp_data
as
--¶¨ÒåÒ»¸öÓαê
DECLARE cur_class CURSOR
 FOR
--´Óstu±íÖлñµÃËùÓеĿÆÄ¿²¢ÇÒÅÅÐò
SELECT distinct class from stu ORDER BY class
--´ò¿ªÓαê
OPEN cur_class
--¶¨ÒåÓαêÑ­»·µÄÐÐÊý
DECLARE @cursor_row INT
--¶¨ÒåÑ­»·¹ý³ÌÖÐÐèÒªµÄ»ñµÃµÄ¿ÆÄ¿Ãû³Æ£¬Óô˱äÁ¿±£´æ
DECLARE @class nvarchar(100)
--ÉèÖÃÓαêµÄÐÐÊý
SET @cursor_row = @@CURSOR_ROWS
--¶¨ÒåÑ­»·ÖÐÐèҪƴ½ÓµÄ×Ö·û´®
declare @sql nvarchar(4000)
declare @insert_sql nvarchar(4000)
declare @case_sql nvarchar(4000)
declare @sum_sql nvarchar(4000)
--´´½¨ÁÙʱ±íµÄÓï¾ä¹Ì¶¨²¿·Ö
set @sql = 'create table #temp (ÐÕÃû nvarchar(100),'
set @insert_sql = 'insert into #temp(ÐÕÃû,'
set @case_sql = ''
set @sum_sql = ''
--print @cursor_row
--°´ÕÕÓαêÑ­»·£¬¼´°´¿ÆÄ¿Ñ­»·
WHILE(@cursor_row > 0)
BEGIN
 SET @cursor_row = @cursor_row - 1
 FETC