--第二个问题.--测试数据
create table 表(列1 varchar(10),列2 int)
insert 表 select 'a',1
union all select 'b',2
union all select 'c',3
union all select 'd',4
union all select 'e',5
union all select 'f',6
union all select 'g',7
union all select 'h',8
go--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''列1='''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+[列1]+'']=''+cast(['+name+'] as varchar) from 表'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'列1'select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go--删除测试
drop table 表/*--测试结果列1 a b c d e f g h
----- ----- ----- ----- ----- ----- ----- ----- -----
列2 1 2 3 4 5 6 7 8(所影响的行数为 1 行)
--*/
create table 表(列1 varchar(10),列2 int)
insert 表 select 'a',1
union all select 'b',2
union all select 'c',3
union all select 'd',4
union all select 'e',5
union all select 'f',6
union all select 'g',7
union all select 'h',8
go--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''列1='''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+[列1]+'']=''+cast(['+name+'] as varchar) from 表'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'列1'select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go--删除测试
drop table 表/*--测试结果列1 a b c d e f g h
----- ----- ----- ----- ----- ----- ----- ----- -----
列2 1 2 3 4 5 6 7 8(所影响的行数为 1 行)
--*/
GOCREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO下面是用于创建旋转结果的 SELECT 语句:SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
set @s=''
select @s=@s+',['+state+']=sum(case state when '''+state+''' then 1 else 0 end)'
from authors
group by state
set @s=substring(@s,2,8000)
exec('select '+@s+' from authors')
group by state
我现在想把行换列。其实我想要的结果就是:
想知道每个城市有多少个作者,但只要一个结果集,而且每个城市做为一个字段
(这样做就不用移动游标就能取到所有要的结果)
大概就是这样我也想过和你差不多的方法,但每个城市都要执行一次select:
declare @s1 int,@s2 int,@s3 int,@s4 int,@s5 int
,@i int
select @s1=0,@s2=0,@s3=0,@s4=0,@s5=0,@i=0
select @s1=(count(state) from authors where city='CA'
...
select @s1,...
解决问题的方法不太好
还想问问您,set变量,和select变量的区别
谢谢
set @s=''
select @s=@s+',['+state+']=sum(case state when '''+state+''' then 1 else 0 end)'
from authors
group by state
exec('select city'+@s+' from authors group by city')
谢谢
可不可以解析下set变量和select变量
select 是为多个变量赋值select @变量=.. from ..
则是从表中查询数据并赋值给变量