表表达式,Substring, CharIndex, 多行数据变同一行的用法
参考:
https://www.cnblogs.com/cnki/p/9561427.html
https://www.cnblogs.com/johnwood/p/6386613.html
1.表1:
CREATE TABLE [dbo].[SQLServerInfo]( [objid] [varchar](50) NULL, [instancename] [varchar](50) NULL ) ON [PRIMARY]
objid instancename
Server1.uuu Instance1
Server2.uuu Instance1
Server3.uuu Instance1
Server4.uuu Instance2
Server5.uuu Instance3
2.表2:
CREATE TABLE [dbo].[WinServers]( [hostname] [varchar](50) NULL, [serverrole] [varchar](50) NULL, [ip] [varchar](50) NULL ) ON [PRIMARY]
hostname serverrole ip Server1 role1 1.1.1.1 Server2 role2 1.1.1.2 Server3 role3 1.1.1.3 Server4 role4 1.1.1.4 Server5 role5 1.1.1.5
3.查询:
With SQLServerInfoView as ( select a.[instancename],b.[serverrole],b.[ip] from [SQLServerInfo] a left join winservers b on substring(a.objid,0,charindex('.',a.objid,0))=b.hostname ) select [instancename], [serverrole] = ( stuff( (select ',' + [serverrole] from SQLServerInfoView where [instancename] = A.[instancename] for xml path('')), 1, 1, '' ) ), [ip] = ( stuff( (select ',' + [ip] from SQLServerInfoView where [instancename] = A.[instancename] for xml path('')), 1, 1, '' ) ) from SQLServerInfoView as A group by [instancename]
作者:BobLiu
邮箱:lzd_ren@hotmail.com
出处:http://www.cnblogs.com/liuzhendong
本文版权归作者所有,欢迎转载,未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
邮箱:lzd_ren@hotmail.com
出处:http://www.cnblogs.com/liuzhendong
本文版权归作者所有,欢迎转载,未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。