if object_id('tempdb..##temp') is not null
drop table ##temp
select * into ##temp from baoyangzq1 where byweihuzhouqi1 is not null and byriqi1 is not null
--行列动态转换
declare @sql varchar(8000)
set @sql='select bymname1,bymno1,byweihuzhouqi1'
select @sql=@sql+',(case '+convert(varchar(10),day(byriqi1),121)+' when '''+convert(varchar(10),day(byriqi1),121)+''' then 1 else 0)'+convert(varchar(10),day(byriqi1),121)+' '
from (select bymname1,bymno1,byweihuzhouqi1,byriqi1 from ##temp) a
set @sql=@sql+'from ##temp group by bymname1,bymno1,byweihzhouqi1'
exec (@sql)所影响的行数为 10 行)
select bymname1,bymno1,byweihuzhouqi1,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 from ##temp group by bymname1,bymno1,byweihzhouqi1
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ')' 附近有语法错误。
请高手们给帮帮,谢谢
drop table ##temp
select * into ##temp from baoyangzq1 where byweihuzhouqi1 is not null and byriqi1 is not null
--行列动态转换
declare @sql varchar(8000)
set @sql='select bymname1,bymno1,byweihuzhouqi1'
select @sql=@sql+',(case '+convert(varchar(10),day(byriqi1),121)+' when '''+convert(varchar(10),day(byriqi1),121)+''' then 1 else 0)'+convert(varchar(10),day(byriqi1),121)+' '
from (select bymname1,bymno1,byweihuzhouqi1,byriqi1 from ##temp) a
set @sql=@sql+'from ##temp group by bymname1,bymno1,byweihzhouqi1'
exec (@sql)所影响的行数为 10 行)
select bymname1,bymno1,byweihuzhouqi1,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 ,(case 3 when '3' then 1 else 0)3 ,(case 5 when '5' then 1 else 0)5 from ##temp group by bymname1,bymno1,byweihzhouqi1
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ')' 附近有语法错误。
请高手们给帮帮,谢谢
drop table ##temp
select * into ##temp from baoyangzq1 where byweihuzhouqi1 is not null and byriqi1 is not null
--行列动态转换
declare @sql varchar(8000)
set @sql='select bymname1,bymno1,byweihuzhouqi1'
select @sql=@sql+',(case '+convert(varchar(10),day(byriqi1),121)+' when '''+convert(varchar(10),day(byriqi1),121)+''' then 1 else 0)['+convert(varchar(10),day(byriqi1),121)+'] '
from (select bymname1,bymno1,byweihuzhouqi1,byriqi1 from ##temp) a
set @sql=@sql+'from ##temp group by bymname1,bymno1,byweihzhouqi1'
exec (@sql)
drop table ##temp
select * into ##temp from baoyangzq1 where byweihuzhouqi1 is not null and byriqi1 is not null
--行列动态转换
declare @sql varchar(8000)
set @sql='select bymname1,bymno1,byweihuzhouqi1'
select @sql=@sql+',max(case ['+convert(varchar(10),day(byriqi1),121)+'] when '''+convert(varchar(10),day(byriqi1),121)+''' then 1 else 0)['+convert(varchar(10),day(byriqi1),121)+'] '
from (select bymname1,bymno1,byweihuzhouqi1,byriqi1 from ##temp) a
set @sql=@sql+'from ##temp group by bymname1,bymno1,byweihzhouqi1'
exec (@sql) 若还有问题,请提供一下表结构和测试数据
ELSE 0 END
这样才OK的。
我的baoyangzq1表结构是
bymno1 int
bymname1 varchar
byriqi1 datetime
byweihuzhouqi1 int