select count(*) AS 总量 ,queueid
from ha_querymember where ha_querymember.status=1 group by queueid总量 queueid
----------- -----------
10 115
4 123
1 125
希望这个 变成
-------------------------
queueid 115 123 125
总量 10 4 1但是写出来不对
declare @sql1 varchar(8000)
select @sql1 = ' max (case queueid when ''' + cast(isnull(queueid,0) as varchar) + ''' then 总量 end) ['
+cast(isnull(queueid,0) as varchar) + ']'
from (select
queueid from ha_querymember ) as a
set @sql1 = 'Select queueid ,'+ @sql1 + ' from ( select count(*) AS 总量 ,queueid
from ha_querymember where ha_querymember.status=1 group by queueid) AS b group by b.queueid 'print @sql1exec(@sql1)queueid 115
----------- -----------
115 10
123 NULL
125 NULL
求解决下
from ha_querymember where ha_querymember.status=1 group by queueid总量 queueid
----------- -----------
10 115
4 123
1 125
希望这个 变成
-------------------------
queueid 115 123 125
总量 10 4 1但是写出来不对
declare @sql1 varchar(8000)
select @sql1 = ' max (case queueid when ''' + cast(isnull(queueid,0) as varchar) + ''' then 总量 end) ['
+cast(isnull(queueid,0) as varchar) + ']'
from (select
queueid from ha_querymember ) as a
set @sql1 = 'Select queueid ,'+ @sql1 + ' from ( select count(*) AS 总量 ,queueid
from ha_querymember where ha_querymember.status=1 group by queueid) AS b group by b.queueid 'print @sql1exec(@sql1)queueid 115
----------- -----------
115 10
123 NULL
125 NULL
求解决下
select @sql1 = isnull(@sql1+',','')+',sum(case when queueid=' + ltrim(queueid)+' then 总量 else 0 end) [' + ltrim(queueid)+']'
from (select distinct queueid from ha_querymember where status=1)tset @sql1='
select queueid,'+@sql1+' from ha_querymember
where status=1
group by queueid'
exec(@sql1)
set @sql='select '
select @sql=@sql+ 'sum(case queueid when '''+queueid+''' then 总量
else 0 end) as '''+queueid+''','from (select distinct queueid from ha_querymember) as a
select @sql
select @sql=left(@sql,len(@sql)-1)+'from ha_querymember where ha_querymember.status=1'select @sql
exec(@sql)
sum(case queueid when 115 then 1 else 0 end) [115],
sum(case queueid when 123 then 1 else 0 end) [123],
sum(case queueid when 125 then 1 else 0 end) [125]
from ha_querymember where ha_querymember.status=1declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' , sum(case queueid when ''' + cast(queueid as varchar) + ''' then 1 else 0 end) [' + queueid + ']'
from (select distinct queueid from ha_querymember where ha_querymember.status=1) as a
set @sql = 'select queueid = ''总量'' , ' + @sql + ' from from ha_querymember where ha_querymember.status=1'
exec(@sql)
消息 245,级别 16,状态 1,第 3 行
将 varchar 值 '' then 总量
else 0 end) as '' 转换为数据类型为 int 的列时发生语法错误。
if object_id('[ha_querymember]') is not null drop table [ha_querymember]
go
create table [ha_querymember] (queueid int,总量 int,status int )
insert into [ha_querymember]
select 123,11,1 union all
select 123,12,1 union all
select 11,33,1 union all
select 34,54,1 union all
select 34,55,0
declare @sql1 varchar(8000)
select @sql1 = isnull(@sql1+',','')+'sum(case when queueid=' + ltrim(queueid)+' then 总量 else 0 end) [' + ltrim(queueid)+']'
from (select distinct queueid from ha_querymember where status=1)tset @sql1='
select id,'+@sql1+'
from
(
select 1 as id,* from ha_querymember
where status=1
)t
group by id'print @sql1
exec(@sql1)
id 11 34 123
----------- ----------- ----------- -----------
1 33 54 23(1 row(s) affected)
set @sql ='select ''总量'' as sum ,'
(select @sql=@sql+' sum( case when queueid='+convert(varchar(100),queueid)+' then 1 else 0 end ) as ['+ convert(varchar(100),queueid) +'] ,' from T_StandardSample_Setup_Detail group by sid) set @sql= substring(@sql,1,len(@sql)-1)
set @sql=@sql+' from ha_querymember '
print @sql
exec (@sql)