SQl ʵÏÖ¶¯Ì¬½»²æ±í²éѯ

 ´´½¨±í
CREATE TABLE [dbo].[ÏúÊÛ±í](
    [ID] [int] NOT NULL,
    [Ô±¹¤ÐÕÃû] [nvarchar](25) COLLATE Chinese_PRC_CI_AS NULL,
    [ËùÔÚ²¿ÃÅ] [nvarchar](15) COLLATE Chinese_PRC_CI_AS NULL,
    [ÏúÊÛÒµ¼¨] [int] NULL
) ON [PRIMARY]
²åÈëÊý¾Ý
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (1,'Àî*Ã÷','·þ×°²¿',2)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (2,'ÖÜ*ÈË','¼Òµç²¿',1)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (3,'º«*','ʳƷ²¿',2)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (4,'º«*','ʳƷ²¿',2)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (5,'˾*ÄÏ','¼Òµç²¿',2)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (6,'º«*ÔË','ʳƷ²¿',5)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (7,'ÖÜ*ÈË','¼Òµç²¿',5)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (8,'Àî*Ã÷','·þ×°²¿',2)
insert into dbo.ÏúÊÛ±í ([ID],[Ô±¹¤ÐÕÃû] ,[ËùÔÚ²¿ÃÅ],[ÏúÊÛÒµ¼¨]) values (9,'Ê·*½ð','ʳƷ²¿',5)
½»²æ±í²éѯ´æ´¢¹ý³Ì
create procedure [dbo].[Corss]
@strTabName as varchar(50) = 'ÏúÊÛ±í',
@strCol as varchar(50) = 'ËùÔÚ²¿ÃÅ',
@strGroup as varchar(50) = 'Ô±¹¤ÐÕÃû',--·Ö×é×Ö¶Î
@strNumber as varchar(50) = 'ÏúÊÛÒµ¼¨', --±»Í³¼ÆµÄ×Ö¶Î
@strSum as varchar(10) = 'Sum' --ÔËË㷽ʽ
AS
DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --Éú³ÉÓαê
begin
SET nocount ON
SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strNumber + ']' --²éѯµÄÇ°°ë¶Î
OPEN corss_cursor
while (0=0)
BEGIN
FETCH NEXT from corss_cursor --±éÀúÓα꣬½«ÁÐÍ·ÐÅÏ¢·ÅÈë±äÁ¿@strTmpCol
INTO @strTmpCol
if (@@fetch_status<>0) break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol +