trySelect ID = Identity(Int, 1, 1), * Into #T From 表Declare @S Varchar(8000) Select @S = 'Select 姓名' Select @S = @S + ', SUM(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 职务 Else '''' End) As 职务' + Cast(OrderID As Varchar) From (Select OrderID = (Select Count(ID) From #T Where 姓名 = A.姓名 And ID <= A.ID), * From #T A) B Group By OrderID Select @S = @S + ' From (Select OrderID = (Select Count(ID) From #T Where 姓名 = A.姓名 And ID <= A.ID), * From #T A) B Group By 姓名' EXEC(@S)Drop Table #T
create table aa ( id int identity(1,1), 姓名 varchar(50), 职务 varchar(50) ) insert into aa values('张三','经理') insert into aa values('张三','主管') insert into aa values('张三','总监') insert into aa values('李四','财务主管') insert into aa values('张三','销售主管') Declare @S Varchar(8000) Select @S = 'Select 姓名' Select @S = @S + ', SUM(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 职务 Else '''' End) As 职务' + Cast(OrderID As Varchar) From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By OrderID Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By 姓名' EXEC(@S)服务器: 消息 409,级别 16,状态 2,行 1 sum or average aggregate 运算不能以 varchar 数据类型作为参数。 服务器: 消息 409,级别 16,状态 1,行 1 sum or average aggregate 运算不能以 varchar 数据类型作为参数。 服务器: 消息 409,级别 16,状态 1,行 1 sum or average aggregate 运算不能以 varchar 数据类型作为参数。 服务器: 消息 409,级别 16,状态 1,行 1 sum or average aggregate 运算不能以 varchar 数据类型作为参数。
修改一個地方即可,沒注意到這個問題,寫順手了。create table aa ( id int identity(1,1), 姓名 varchar(50), 職務 varchar(50) ) insert into aa values('張三','經理') insert into aa values('張三','主管') insert into aa values('張三','總監') insert into aa values('李四','財務主管') insert into aa values('張三','銷售主管') GO Declare @S Varchar(8000) Select @S = 'Select 姓名' Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 職務 Else '''' End) As 職務' + Cast(OrderID As Varchar) From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By OrderID Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By 姓名' Print @S EXEC(@S) GO Drop Table aa
create table aa ( id int identity(1,1), 姓名 varchar(50), 職務 varchar(50) ) insert into aa values('張三','經理') insert into aa values('張三','主管') insert into aa values('張三','總監') insert into aa values('李四','財務主管') insert into aa values('張三','銷售主管') insert into aa values('張三','营业主管') insert into aa values('李四','人事主管') insert into aa values('张三','生活主管')GO Declare @S Varchar(8000) Select @S = 'Select 姓名' Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 職務 Else '''' End) As 職務' + Cast(OrderID As Varchar) From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) B Group By OrderID Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) as B Group By 姓名' Print @S EXEC(@S) GO执行结果有点问题
那是因為你的數據裡有繁體的,也有簡體的,全部改為簡體的,就沒有問題了。 create table aa ( id int identity(1,1), 姓名 varchar(50), 职务 varchar(50) ) insert into aa values('张三','经理') insert into aa values('张三','主管') insert into aa values('张三','总监') insert into aa values('李四','财务主管') insert into aa values('张三','销售主管') insert into aa values('张三','营业主管') insert into aa values('李四','人事主管') insert into aa values('张三','生活主管')GO Declare @S Varchar(8000) Select @S = 'Select 姓名' Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 职务 Else '''' End) As 职务' + Cast(OrderID As Varchar) From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) B Group By OrderID Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) as B Group By 姓名' Print @S EXEC(@S) GO Drop Table aa
Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 职务 Else '''' End) As 职务' + Cast(OrderID As Varchar) From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) B Group By OrderID 这种select from 加付值得语句没见过,请鱼老大讲讲
Select @S = 'Select 姓名'
Select @S = @S + ', SUM(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 职务 Else '''' End) As 职务' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(ID) From #T Where 姓名 = A.姓名 And ID <= A.ID), * From #T A) B Group By OrderID
Select @S = @S + ' From (Select OrderID = (Select Count(ID) From #T Where 姓名 = A.姓名 And ID <= A.ID), * From #T A) B Group By 姓名'
EXEC(@S)Drop Table #T
create table aa
(
id int identity(1,1),
姓名 varchar(50),
职务 varchar(50)
)
insert into aa values('张三','经理')
insert into aa values('张三','主管')
insert into aa values('张三','总监')
insert into aa values('李四','财务主管')
insert into aa values('张三','销售主管')
Declare @S Varchar(8000)
Select @S = 'Select 姓名'
Select @S = @S + ', SUM(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 职务 Else '''' End) As 职务' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By OrderID
Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By 姓名'
EXEC(@S)服务器: 消息 409,级别 16,状态 2,行 1
sum or average aggregate 运算不能以 varchar 数据类型作为参数。
服务器: 消息 409,级别 16,状态 1,行 1
sum or average aggregate 运算不能以 varchar 数据类型作为参数。
服务器: 消息 409,级别 16,状态 1,行 1
sum or average aggregate 运算不能以 varchar 数据类型作为参数。
服务器: 消息 409,级别 16,状态 1,行 1
sum or average aggregate 运算不能以 varchar 数据类型作为参数。
(
id int identity(1,1),
姓名 varchar(50),
職務 varchar(50)
)
insert into aa values('張三','經理')
insert into aa values('張三','主管')
insert into aa values('張三','總監')
insert into aa values('李四','財務主管')
insert into aa values('張三','銷售主管')
GO
Declare @S Varchar(8000)
Select @S = 'Select 姓名'
Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 職務 Else '''' End) As 職務' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By OrderID
Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa A) B Group By 姓名'
Print @S
EXEC(@S)
GO
Drop Table aa
(
id int identity(1,1),
姓名 varchar(50),
職務 varchar(50)
)
insert into aa values('張三','經理')
insert into aa values('張三','主管')
insert into aa values('張三','總監')
insert into aa values('李四','財務主管')
insert into aa values('張三','銷售主管')
insert into aa values('張三','营业主管')
insert into aa values('李四','人事主管')
insert into aa values('张三','生活主管')GO
Declare @S Varchar(8000)
Select @S = 'Select 姓名'
Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 職務 Else '''' End) As 職務' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) B Group By OrderID
Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) as B Group By 姓名'
Print @S
EXEC(@S)
GO执行结果有点问题
create table aa
(
id int identity(1,1),
姓名 varchar(50),
职务 varchar(50)
)
insert into aa values('张三','经理')
insert into aa values('张三','主管')
insert into aa values('张三','总监')
insert into aa values('李四','财务主管')
insert into aa values('张三','销售主管')
insert into aa values('张三','营业主管')
insert into aa values('李四','人事主管')
insert into aa values('张三','生活主管')GO
Declare @S Varchar(8000)
Select @S = 'Select 姓名'
Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then 职务 Else '''' End) As 职务' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) B Group By OrderID
Select @S = @S + ' From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) as B Group By 姓名'
Print @S
EXEC(@S)
GO
Drop Table aa
From (Select OrderID = (Select Count(ID) From aa Where 姓名 = A.姓名 And ID <= A.ID), * From aa as A) B Group By OrderID
这种select from 加付值得语句没见过,请鱼老大讲讲