现有一表，结构如下：
name code level
小刘 020315 严重
小刘 020316 严重
小刘 020100 一般
小王 020100 一般
小张 020316 严重
小张 020101 一般
小李
要得到如下结果：
name code level
小刘 020100、020315、020316 严重
小王 020100 一般
小张 020101、020316 严重
小李 无
应该如何写呢?求多种方法，大家集思广议啊
------解决方案--------------------
- SQL code
create table myl(name varchar(8), code varchar(8), level varchar(6))insert into mylselect '小刘', '020315', '严重' union allselect '小刘', '020316', '严重' union allselect '小刘', '020100', '一般' union allselect '小王', '020100', '一般' union allselect '小张', '020316', '严重' union allselect '小张', '020101', '一般' union allselect '小李', '', ''select a.name, stuff((select ','+b.code from myl b where a.name=b.name for xml path('')),1,1,'') as 'code', case min(level) when '' then '无' else min(level) end as 'level'from myl agroup by a.namename code level-------- ----------------------- ------小李 无小刘 020315,020316,020100 严重小王 020100 一般小张 020316,020101 严重(4 row(s) affected)
------解决方案--------------------
SQL2000写法,
- SQL code
create table myl(name varchar(8), code varchar(8), level varchar(6))insert into mylselect '小刘', '020315', '严重' union allselect '小刘', '020316', '严重' union allselect '小刘', '020100', '一般' union allselect '小王', '020100', '一般' union allselect '小张', '020316', '严重' union allselect '小张', '020101', '一般' union allselect '小李', '', ''create function dbo.fn_myl(@name varchar(8))returns varchar(6000)asbegin declare @codes varchar(6000)='' select @[email protected]+code+',' from myl where [email protected] select @codes=left(@codes,len(@codes)-1) return @codesendselect name, dbo.fn_myl(name) as 'code', case min(level) when '' then '无' else min(level) end as 'level'from mylgroup by namename code level-------- ------------------------- ------小李 无小刘 020315,020316,020100 严重小王 020100 一般小张 020316,020101 严重(4 row(s) affected)
------解决方案--------------------
- SQL code
[Quote=引用:]高手们，我用的是SQLServer2000[/Quote]--> 测试数据：[ta]if object_id('[ta]') is not null drop table [ta]go create table [ta]([name] varchar(4),[code] varchar(6),[level] varchar(4))insert [ta]select '小刘','020315','严重' union allselect '小刘','020316','严重' union allselect '小刘','020100','一般' union allselect '小王','020100','一般' union allselect '小张','020316','严重' union allselect '小张','020101','一般' union allselect '小李',null,null--------------开始查询--------------------------if object_id('F_Str') is not null drop function F_Str go create function F_Str(@name varchar(10)) returns nvarchar(50) as begin declare @s nvarchar(100) select @s=isnull(@S+',','')+[code] from [ta] where [name][email protected] return @s end go select [name],dbo.F_Str([name]),isnull(min([level]),'无')from tagroup by [name]go ----------------结果----------------------------/* name ---- -------------------------------------------------- ----小李 NULL 无小刘 020315,020316,020100 严重小王 020100 一般小张 020316,020101 严重(4 行受影响)*/