create table table1 (id int,equipmentid varchar(5),[size] varchar(50),quantity int,addtime varchar(10) ) INSERT INTO table1 select 1,'1','S',50,'7:00' union all select 2,'2','M',10,'11:00' union all select 3,'2','L',20,'7:00' union all select 4,'4','S',100,'9:00' union all select 5,'1','L',87,'13:00' union all select 6,'3','L',90,'11:00' union all select 7,'3','S',10,'7:00'declare @s1 nvarchar(4000) select @s1=''select @s1=@s1+','+quotename(equipmentid+'#(size)')+'=max(case when equipmentid='''+equipmentid+''' then [size] end)' +','+quotename(equipmentid+'#(quantity)')+'=sum(case when equipmentid='''+equipmentid+''' then [quantity] end)' from table1 group by equipmentidselect @s1='select addtime' + @s1 + ' from table1 group by addtime order by len(addtime),addtime' exec(@s1)drop table table1
把equipmentid 轉換成varchar型:cast(equipmentid as varchar(10)) 參考: create table table1 (id int,equipmentid int,[size] decimal(18,3),quantity int,addtime varchar(10) ) INSERT INTO table1 select 1,1,1,50,'7:00' union all select 2,2,2,10,'11:00' union all select 3,2,4,20,'7:00' union all select 4,4,1,100,'9:00' union all select 5,1,9,87,'13:00' union all select 6,3,6,90,'11:00' union all select 7,3,6,10,'7:00'declare @s1 nvarchar(4000) select @s1=''select @s1=@s1+','+quotename(cast(equipmentid as varchar(10))+'#(size)')+'=max(case when equipmentid='''+cast(equipmentid as varchar(10))+''' then [size] end)' +','+quotename(cast(equipmentid as varchar(10))+'#(quantity)')+'=sum(case when equipmentid='''+cast(equipmentid as varchar(10))+''' then [quantity] end)' from table1 group by equipmentidselect @s1='select addtime' + @s1 + ' from table1 group by addtime order by len(addtime),addtime' exec(@s1)drop table table1
INSERT INTO table1
select 1,'1','S',50,'7:00'
union all select 2,'2','M',10,'11:00'
union all select 3,'2','L',20,'7:00'
union all select 4,'4','S',100,'9:00'
union all select 5,'1','L',87,'13:00'
union all select 6,'3','L',90,'11:00'
union all select 7,'3','S',10,'7:00'declare @s1 nvarchar(4000)
select @s1=''select @s1=@s1+','+quotename(equipmentid+'#(size)')+'=max(case when equipmentid='''+equipmentid+''' then [size] end)'
+','+quotename(equipmentid+'#(quantity)')+'=sum(case when equipmentid='''+equipmentid+''' then [quantity] end)'
from table1 group by equipmentidselect @s1='select addtime' + @s1 + ' from table1 group by addtime order by len(addtime),addtime'
exec(@s1)drop table table1
能说一下你是怎么理解的吗
我试了一下可以分都给你。
呵呵
将 varchar 值 '#(size)' 转换为数据类型为 int 的列时发生语法错误。
我的size是 number类型得数据
请问怎么转换
參考:
create table table1 (id int,equipmentid int,[size] decimal(18,3),quantity int,addtime varchar(10) )
INSERT INTO table1
select 1,1,1,50,'7:00'
union all select 2,2,2,10,'11:00'
union all select 3,2,4,20,'7:00'
union all select 4,4,1,100,'9:00'
union all select 5,1,9,87,'13:00'
union all select 6,3,6,90,'11:00'
union all select 7,3,6,10,'7:00'declare @s1 nvarchar(4000)
select @s1=''select @s1=@s1+','+quotename(cast(equipmentid as varchar(10))+'#(size)')+'=max(case when equipmentid='''+cast(equipmentid as varchar(10))+''' then [size] end)'
+','+quotename(cast(equipmentid as varchar(10))+'#(quantity)')+'=sum(case when equipmentid='''+cast(equipmentid as varchar(10))+''' then [quantity] end)'
from table1 group by equipmentidselect @s1='select addtime' + @s1 + ' from table1 group by addtime order by len(addtime),addtime'
exec(@s1)drop table table1