多行变少行

Question

表tb中有字段JIKGUBCD，现在想把字段中的值包含有开头是 CA CB CC合一起，CD CE合一起，CF合一起
如
JIKGUBCD
CAA
CAB
CBA
CBB
CCA
CCB
CDA
CDB
CEA
CEB
CFA
CFB
CFC
CFD
想得到
CAA|CAB|CBA|CBB|CCA|CCB
CDA|CDB|CEA|CEB
CFA|CFB|CFC|CFD如果是JIKGUBCD
CAA
CBA
CBB
CCA
CCB
CDA
CDB
CEA
CEB
CFA
CFC
CFD
想得到
CAA|CBA|CBB|CCA|CCB
CDA|CDB|CEA|CEB
CFA|CFC|CFD

Answer 1

create table tb(JIKGUBCD nvarchar(10))
insert into tb select 'CAA'
insert into tb select 'CAB'
insert into tb select 'CBA'
insert into tb select 'CBB'
insert into tb select 'CCA'
insert into tb select 'CCB'
insert into tb select 'CDA'
insert into tb select 'CDB'
insert into tb select 'CEA'
insert into tb select 'CEB'
insert into tb select 'CFA'
insert into tb select 'CFB'
insert into tb select 'CFC'
insert into tb select 'CFD'
go
;with cte as(
select JIKGUBCD,abs(ASCII(substring(JIKGUBCD,2,1))-66)/2 as fz from tb
)select stuff((select '|'+JIKGUBCD from cte where fz=a.fz for xml path('')),1,1,'') from cte a group by fz
go
drop table tb
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CAA|CAB|CBA|CBB|CCA|CCB
CDA|CDB|CEA|CEB
CFA|CFB|CFC|CFD(3 行受影响)
*/

Answer 2

with cte as是什么意思　？
for xml　有什么作用?

Answer 3

2楼 xml用的 真是太 妙了 佩服

Answer 4

2000用函数IF (OBJECT_ID('TB')>0)
BEGIN
DROP TABLE TB
END
--处理的数据
CREATE TABLE tb(
col1 varchar(10),
col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO--合并处理函数
CREATE FUNCTION dbo.f_str(
@col1 varchar(10)
)RETURNS varchar(100)
AS
BEGIN
DECLARE
@re varchar(100)
SET @re = ''
SELECT
@re = @re + ',' + CAST(col2 as varchar)
FROM tb
WHERE col1 = @col1 RETURN(STUFF(@re, 1, 1, ''))
END
GO--调用函数
SELECT
col1,
col2 = dbo.f_str(col1)
FROM tb
GROUP BY col1--删除测试
DROP TABLE tb
DROP FUNCTION dbo.f_str
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/

Answer 5

但我想前两位是　ＣＡ　　ＣＢ　　ＣＣ时合并
前两位是CD CE时合并
前两位是CF时合并　呢?
这样传过去的变量值是不一样的

Answer 6

2000也可以呀
with cte as其实就是定义1个临时表，不过那是2005的用法
2000的话，你可以select JIKGUBCD,abs(ASCII(substring(JIKGUBCD,2,1))-66)/2 as fz into #cte from tb,定义个#cte的临时表
至于for xml path 2000的用法是一样的顺便佩服下2楼的算法 

Answer 7

那不能
select JIKGUBCD,abs(ASCII(substring(JIKGUBCD,2,1))-66)/2 as fz into #cte from tb
select stuff((select '|'+JIKGUBCD from #cte  where fz=a.fz for xml path('')),1,1,'') from #cte  a group by fz

Answer 8

是不是要作啥设置？
老是提示
Incorrect syntax near 'xml'.

Answer 9

for xml path　　是不是得2005才能用的，2000用不了

Answer 10

create table tb(JIKGUBCD nvarchar(10))
insert into tb select 'CAA'
insert into tb select 'CAB'
insert into tb select 'CBA'
insert into tb select 'CBB'
insert into tb select 'CCA'
insert into tb select 'CCB'
insert into tb select 'CDA'
insert into tb select 'CDB'
insert into tb select 'CEA'
insert into tb select 'CEB'
insert into tb select 'CFA'
insert into tb select 'CFB'
insert into tb select 'CFC'
insert into tb select 'CFD'
go--合并处理函数
CREATE FUNCTION dbo.f_str(
    @col1 varchar(10)
)RETURNS varchar(100)
AS
BEGIN
    DECLARE
        @re varchar(100)
    SET @re = ''
    SELECT
        @re = @re + '|' + CAST(JIKGUBCD as varchar)
    FROM (select JIKGUBCD,id=case substring(JIKGUBCD,1,2) when 'CA' then 1 when 'CB' then 1 when 'CC' then 1 
when 'CD' then 2 when 'CE' then 2 
when 'CF' then 3 end   from tb) t
    WHERE id = @col1    RETURN(STUFF(@re, 1, 1, ''))
END
GO--调用函数
SELECT
    id,
    JIKGUBCD = dbo.f_str(id)
FROM (select JIKGUBCD,id=case substring(JIKGUBCD,1,2) when 'CA' then 1 when 'CB' then 1 when 'CC' then 1 
when 'CD' then 2 when 'CE' then 2 
when 'CF' then 3 end from tb) t
GROUP BY id
/**
id          JIKGUBCD
----------- ----------------------------------------------------------------------------------------------------
1           CAA|CAB|CBA|CBB|CCA|CCB
2           CDA|CDB|CEA|CEB
3           CFA|CFB|CFC|CFD(3 行受影响)
**/

Answer 11

用１８楼的
出来是1  NULL
2  NULL
3  NULL