27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-07-13 09:07:06
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([ZS] varchar(2),[LB] varchar(2),[BL] varchar(3),[BH] varchar(3))
insert [TB]
select '90','AA','50','1-1' union all
select '70','BB',null,'1-1' union all
select 'CC','60',null,'1-1' union all
select '80','AA','75','1-2' union all
select 'BB','30',null,'1-2'
--------------开始查询--------------------------
select bh, [YL]=stuff((select '*'+[zs]+[LB]+isnull(BL,'') from tb t where BH=tb.BH for xml path('')), 1, 1, '')
from tb
group by BH
----------------结果----------------------------
/*bh YL
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1-1 90AA50*70BB*CC60
1-2 80AA75*BB30
(2 行受影响)
*/