| ◎--和行转列的应用效果差不多了，统计结果...... --和行转列的应用效果差不多了，统计结果中可能会用到 --测试表 create table Test (F1 varchar(10), F2 varchar(10)) --插入数据 insert into Test select 'jack' F1,'book1' F2 union select 'jack' F1,'book2' F2 union select 'jack' F1,'book3' F2 union select 'Mary' F1,'book4' F2 union select 'Mary' F1,'book5' F2 union select 'Mike' F1,'book1' F2 union select 'Mike' F1,'book5' F2 union select 'Mike' F1,'book7' F2 union select 'Mike' F1,'book9' F2 --一条动态SQL语句 go --合并函数 CREATE FUNCTION MergeCharField(@Group varchar(255)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r='' SELECT @r=@r+','+rtrim(F2) FROM Test WHERE F1=@Group RETURN(substring(@r,2,8000)) END GO --调用 select F1 [name],dbo.MergeCharField(F1) [book] from test group by F1 --删除测试环境 drop table test drop FUNCTION MergeCharField /* name book -------------------------- jack book1,book2,book3 Mary book4,book5 Mike book1,book5,book7,book9 */ |
|小黑屋|最新主题|手机版|微赢网络技术论坛
( 苏ICP备08020429号 )
GMT+8, 2024-9-29 15:28 , Processed in 0.085931 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.
