22,206
社区成员
发帖
与我相关
我的任务
分享
-->测试数据
declare @test table(col1 varchar(1),col2 int,col3 varchar(3))
insert into @test
select 'A',1,'' union all
select 'A',1,'001' union all
select 'A',2,'006' union all
select 'A',2,'003' union all
select 'B',1,'007' union all
select 'B',1,'005'
-->开始查询
select * from
(
select t.col1,
t.col2,
col3=(case when isnull(t.col3,'')<>'' then
stuff((select ','+col3 from @test
where isnull(col3,'')<>''
and t.col1=col1
and t.col2=col2 for xml path('')),1,1,'')
else t.col3 end)
from @test t
)tt
group by col1,col2,col3
-->结果集
/*
col1 col2 col3
---- ----------- -------------
A 1
A 1 001
A 2 006,003
B 1 007,005
*/
/*字段1 字段2 字段3
A 1
A 1 001
A 2 006
A 2 003
B 1 007
B 1 005
*/
CREATE TABLE TABLE7(ID VARCHAR(1),Type INT, LIN VARCHAR(10))
INSERT INTO TABLE7
SELECT 'A',1 ,NULL UNION ALL
SELECT 'A',1,'001' UNION ALL
SELECT 'A',2,'006' UNION ALL
SELECT 'A',2,'003' UNION ALL
SELECT 'B',1,'007' UNION ALL
SELECT 'B',1,'005'
SELECT * FROM TABLE7
CREATE FUNCTION S_TR(@ID VARCHAR(1),@Type INT)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @RET VARCHAR(200)
SET @RET = ''
SELECT @RET = @RET+','+LIN+',' from TABLE7 where ID = @ID AND Type =@Type
SET @RET = STUFF(@RET,1,1,'')
RETURN @RET
END
SELECT ID,TYPE,dbo.S_TR(ID,TYPE) FROM TABLE7 GROUP BY ID,TYPE
/*
ID TYPE
---- ----------- --------------
A 1 NULL
A 2 006,,003,
B 1 007,,005,
(3 行受影响)
*/
declare @T table (字段1 varchar(1),字段2 int,字段3 varchar(3))
insert into @T
select 'A',1,null union all
select 'A',1,'001' union all
select 'A',2,'006' union all
select 'A',2,'003' union all
select 'B',1,'007' union all
select 'B',1,'005'
select
字段1,字段2,[字段3]
from @T where 字段3 is null
union all
select
字段1,字段2,[字段3]=
stuff((select ','+字段3 from @T t
where 字段2=a.字段2 and 字段1=a.字段1 for xml path('')), 1, 1, '')
from @T a where 字段3 is not null
group by 字段1, 字段2
/*
字段1 字段2 字段3
---- ----------- ------------
A 1 NULL
A 1 001
A 2 006,003
B 1 007,005
*/
declare @test table(col1 varchar(1),col2 int,col3 varchar(3))
insert into @test
select 'A',1,'' union all
select 'A',1,'001' union all
select 'A',2,'006' union all
select 'A',2,'003' union all
select 'B',1,'007' union all
select 'B',1,'005'
select * from
(
select t.col1,
t.col2,
col3=(case when isnull(t.col3,'')<>'' then
stuff((select ','+col3 from @test
where isnull(col3,'')<>''
and t.col1=col1
and t.col2=col2 for xml path('')),1,1,'')
else t.col3 end)
from @test t
)tt
group by col1,col2,col3
/*
col1 col2 col3
---- ----------- -------------
A 1
A 1 001
A 2 006,003
B 1 007,005
*/