多条记录并为变成一条记录（有点怪）

Question

CREATE TABLE tb
(
  col VARCHAR(20),
  col1 VARCHAR(20)
)INSERT INTO tb 
  SELECT '联赛A',NULL UNION ALL
  SELECT '主队A1','比分00' UNION ALL
  SELECT '客队A2',NULL UNION ALL
  SELECT '联赛B',NULL UNION ALL
  SELECT '主队B1','比分01' UNION ALL
  SELECT '客队B2',NULL
SELECT COL1 = A.col, COL2 = B.col, COL3 = C.COL, COL4 = ISNULL(A.col1,ISNULL(C.col1,B.col1))
  FROM
(
SELECT ID = CASE WHEN LEN(RTRIM(STUFF(col,1,PATINDEX('%[A-Za-z]%',LTRIM(col)),''))) = 0
             THEN 0
             ELSE STUFF(col,1,PATINDEX('%[A-Za-z]%',LTRIM(col)),'')
             END,
       M = SUBSTRING(col,PATINDEX('%[A-Za-z]%',LTRIM(col)),1),
       *
  FROM tb
) A
INNER JOIN
(
SELECT ID = CASE WHEN LEN(RTRIM(STUFF(col,1,PATINDEX('%[A-Za-z]%',LTRIM(col)),''))) = 0
             THEN 0
             ELSE STUFF(col,1,PATINDEX('%[A-Za-z]%',LTRIM(col)),'')
             END,
       M = SUBSTRING(col,PATINDEX('%[A-Za-z]%',LTRIM(col)),1),
       *
  FROM tb
) B ON A.M = B.M
INNER JOIN
(
SELECT ID = CASE WHEN LEN(RTRIM(STUFF(col,1,PATINDEX('%[A-Za-z]%',LTRIM(col)),''))) = 0
             THEN 0
             ELSE STUFF(col,1,PATINDEX('%[A-Za-z]%',LTRIM(col)),'')
             END,
       M = SUBSTRING(col,PATINDEX('%[A-Za-z]%',LTRIM(col)),1),
       *
  FROM tb
) C ON A.M = C.M
WHERE A.id = 0 AND CHARINDEX('主',B.col) >0 AND CHARINDEX('客',C.col)>0 DROP TABLE tb
（所影响的行数为 6 行）COL1                 COL2                 COL3                 COL4                 
-------------------- -------------------- -------------------- -------------------- 
联赛A                  主队A1                 客队A2                 比分00
联赛B                  主队B1                 客队B2                 比分01（所影响的行数为 2 行）

Answer 1

use test
gocreate table t(col1 nvarchar(20),col2 nvarchar(20))
insert t select '联赛A',   null 
insert t select '主队a1',  '比分00' 
insert t select '客队a2',  null 
insert t select '联赛B',   null 
insert t select '主队b1',  '比分01' 
insert t select '客队b2',  null go
create function test_f1(@col1 nvarchar(20))
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+' ','')+col1 from t 
where upper(right(col1,2))=right('联赛A',1)+'1' or upper(right(col1,2))=right('联赛A',1)+'2'
return @s
end
go
create function test_f2(@col1 nvarchar(20))
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+' ','')+col2 from t 
where upper(right(col1,2))=right('联赛A',1)+'1' 
return @s
end
go
select col1,[显示]=dbo.test_f1(col1)+dbo.test_f2(col1) from t where col1 like '联赛%'
col1                 显示                                                                                                                                                                                                       
-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
联赛A                  主队a1 客队a2比分00
联赛B                  主队a1 客队a2比分00（所影响的行数为 2 行）

Answer 2

把函数里的联赛A替换为变量:
go
create function test_f1(@col1 nvarchar(20))
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+' ','')+col1 from t 
where upper(right(col1,2))=right(@col1,1)+'1' or upper(right(col1,2))=right(@col1,1)+'2'
return @s
end
go
create function test_f2(@col1 nvarchar(20))
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+' ','')+col2 from t 
where upper(right(col1,2))=right(@col1,1)+'1' 
return @s
end
go
select col1,[显示]=dbo.test_f1(col1)+dbo.test_f2(col1) from t where col1 like '联赛%'--drop function test_f1,test_f2col1                 显示                                                                                                                                                                                                       
-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
联赛A                  主队a1 客队a2比分00
联赛B                  主队b1 客队b2比分01（所影响的行数为 2 行）