USE [test] GO /****** Object: Table [dbo].[a] Script Date: 12/07/2006 16:12:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[a]( [a] [varchar](50) NULL, [b] [varchar](50) NULL ) ON [PRIMARY] GO INSERT A SELECT 'a' ,1 INSERT A SELECT 'b' ,2 INSERT A SELECT 'b' ,3 INSERT A SELECT 'c' ,4 INSERT A SELECT 'c' ,5 INSERT A SELECT 'c' ,6 INSERT A SELECT 'c' ,7GO SET ANSI_PADDING OFFDECLARE @aa TABLE ( a varchar(50), b varchar(50) ) DECLARE @TEMP_A VARCHAR(50),@TEMP_STR VARCHAR(8000) DECLARE temp_Cursor CURSOR FOR SELECT a from a GROUP BY A OPEN temp_Cursor; FETCH NEXT FROM temp_Cursor into @TEMP_A; WHILE @@FETCH_STATUS = 0 BEGIN
SET @TEMP_STR='' SELECT @TEMP_STR=@TEMP_STR+B FROM A WHERE a=@TEMP_A INSERT @aa (a,b) select @TEMP_A,@TEMP_STR FETCH NEXT FROM temp_Cursor INTO @TEMP_A; END; CLOSE temp_Cursor; DEALLOCATE temp_Cursor; SELECT * FROM @aaGO
呵呵,琢磨了两个多小时才搞出来,表数据都给你建好了,直接拷贝到SQL查询里面就可以用。 你要多给点分啊。^-^ create table #tt (name1 varchar(10),val int) insert into #tt(name1,val) values('a',1) insert into #tt(name1,val) values('b',2) insert into #tt(name1,val) values('b',3) insert into #tt(name1,val) values('c',4) insert into #tt(name1,val) values('c',5) insert into #tt(name1,val) values('c',6) --查询语句如下 declare @sql as varchar(2000) declare @sql1 as varchar(4000) set @sql='select name1' select @sql=@sql+',sum(case when val='+cast(val as varchar(5))+' then val end) as ['+cast(val as varchar(5))+']' from #tt set @sql=@sql+' from #tt group by name1' set @sql1='select ''''' select @sql1=@sql1+'+isnull(cast(a.['+cast(val as varchar(5))+'] as varchar(5)),'''')' from #tt set @sql1=@sql1+' from ('+@sql+') as a' exec(@sql1) --另外用游标也可以搞出来,语法原理和上面一样,只是一般情况下不建议用游标
呵呵,忘记了返回结果集要包含两个字段了,上面的查询语句用下面的替换 --查询语句如下 declare @sql as varchar(2000) declare @sql1 as varchar(4000) set @sql='select name1' select @sql=@sql+',sum(case when val='+cast(val as varchar(5))+' then val end) as ['+cast(val as varchar(5))+']' from #tt set @sql=@sql+' from #tt group by name1' set @sql1='select name1,val=''''' select @sql1=@sql1+'+isnull(cast(a.['+cast(val as varchar(5))+'] as varchar(5)),'''')' from #tt set @sql1=@sql1+' from ('+@sql+') as a' exec(@sql1)
GO
/****** Object: Table [dbo].[a] Script Date: 12/07/2006 16:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[a](
[a] [varchar](50) NULL,
[b] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT A SELECT 'a' ,1
INSERT A SELECT 'b' ,2
INSERT A SELECT 'b' ,3
INSERT A SELECT 'c' ,4
INSERT A SELECT 'c' ,5
INSERT A SELECT 'c' ,6
INSERT A SELECT 'c' ,7GO
SET ANSI_PADDING OFFDECLARE @aa TABLE
(
a varchar(50),
b varchar(50)
)
DECLARE @TEMP_A VARCHAR(50),@TEMP_STR VARCHAR(8000)
DECLARE temp_Cursor CURSOR FOR SELECT a from a GROUP BY A OPEN temp_Cursor;
FETCH NEXT FROM temp_Cursor into @TEMP_A;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TEMP_STR=''
SELECT @TEMP_STR=@TEMP_STR+B FROM A WHERE a=@TEMP_A
INSERT @aa (a,b)
select @TEMP_A,@TEMP_STR
FETCH NEXT FROM temp_Cursor INTO @TEMP_A;
END;
CLOSE temp_Cursor;
DEALLOCATE temp_Cursor;
SELECT * FROM @aaGO
你要多给点分啊。^-^
create table #tt
(name1 varchar(10),val int)
insert into #tt(name1,val) values('a',1)
insert into #tt(name1,val) values('b',2)
insert into #tt(name1,val) values('b',3)
insert into #tt(name1,val) values('c',4)
insert into #tt(name1,val) values('c',5)
insert into #tt(name1,val) values('c',6)
--查询语句如下
declare @sql as varchar(2000)
declare @sql1 as varchar(4000)
set @sql='select name1'
select @sql=@sql+',sum(case when val='+cast(val as varchar(5))+' then val end) as ['+cast(val as varchar(5))+']'
from #tt
set @sql=@sql+' from #tt group by name1'
set @sql1='select '''''
select @sql1=@sql1+'+isnull(cast(a.['+cast(val as varchar(5))+'] as varchar(5)),'''')'
from #tt
set @sql1=@sql1+' from ('+@sql+') as a'
exec(@sql1)
--另外用游标也可以搞出来,语法原理和上面一样,只是一般情况下不建议用游标
--查询语句如下
declare @sql as varchar(2000)
declare @sql1 as varchar(4000)
set @sql='select name1'
select @sql=@sql+',sum(case when val='+cast(val as varchar(5))+' then val end) as ['+cast(val as varchar(5))+']'
from #tt
set @sql=@sql+' from #tt group by name1'
set @sql1='select name1,val='''''
select @sql1=@sql1+'+isnull(cast(a.['+cast(val as varchar(5))+'] as varchar(5)),'''')'
from #tt
set @sql1=@sql1+' from ('+@sql+') as a'
exec(@sql1)