一种解决方案是创建 UDF 使用一个 FOR XML PATH 表达式。

• udf处理一个短语id的连接。
• 它可以用于正常选择

SQL语句

SELECT  PhraseID, dbo.UDF_ConcatWord(PhraseID)
FROM    Phrases
GROUP BY PhraseID

创建自定义项

CREATE FUNCTION dbo.UDF_ConcatWord(@phraseID INT) RETURNS VARCHAR(8000) AS
BEGIN  
  DECLARE @r VARCHAR(8000)
  SELECT @r = (
    SELECT  word + ', '
    FROM    Phrases
    WHERE   phraseID = @phraseID
    FOR XML PATH('')
  )
  IF LEN(@r) > 0 SET @r = SUBSTRING(@r, 1, LEN(@r)-1)
  RETURN @r
END
GO

编辑

在自己修改了一些链接之后,一个更短的解决方案是

SQL语句

SELECT  DISTINCT p1.PhraseID
        , STUFF(( SELECT  ' ' + p2.word 
                  FROM    Phrases AS p2 
                  WHERE   p2.PhraseID = p1.PhraseID 
                  FOR XML PATH('')), 1, 1, '') AS completePhrase
FROM      Phrases AS p1
ORDER BY  p1.PhraseID

试试这个:

DECLARE @TableA  table (RowID int, phraseId varchar(5),seqNum int, word varchar(5))

INSERT INTO @TableA VALUES (1,1,1,'hello')
INSERT INTO @TableA VALUES (2,1,2,'world')
INSERT INTO @TableA VALUES (3,2,1,'black')
INSERT INTO @TableA VALUES (4,2,2,'and')
INSERT INTO @TableA VALUES (5,2,3,'white')

SELECT
    c1.phraseId
        ,STUFF(
                 (SELECT
                      ' ' + word
                      FROM @TableA  c2
                      WHERE c2.phraseId=c1.phraseId
                      ORDER BY c1.phraseId, seqNum
                      FOR XML PATH('') 
                 )
                 ,1,1, ''
              ) AS CombinedValue
    FROM @TableA c1
    GROUP BY c1.phraseId
    ORDER BY c1.phraseId

输出:

phraseId CombinedValue
-------- --------------------------
1        hello world
2        black and white

(2 row(s) affected)

假设你有一个表保存每个短语的头记录,我有点欺骗了你。如果缺少,可以通过从包含以下单词的表中选择不同的短语列表来构造:

declare @words table
(id int
,phraseId int
,seqNum int
,word varchar(10)
)

insert @words
select 1,1,1,'hello'
union select 2,1,2,'world'
union select 3,2,1,'black'
union select 4,2,2,'and'
union select 5,2,4,'white'

declare @phrase table
(phraseId int)

insert @phrase
select 1
union select 2

select phraseID
       ,phraseText AS completePhrase
FROM @phrase AS p
CROSS APPLY (select word + ' ' as [text()]
             from @words AS w
             where w.phraseID = p.phraseID
             for xml path('')
            ) as phrases (phraseText)

最后,我使用了lieven的第二个答案,但是发现对于某些字符串组合, FOR XML PATH('') 诀窍导致问题发生:

declare @phrases table
(
    id int
    ,phraseId int
    ,seqNum int
    ,word varchar(10)
)

insert 
    @phrases 
values
    (1,1,1,'hello'),
    (2,1,2,'world'),
    (3,2,1,'black'),
    (4,2,2,'and'),
    (5,2,3,'white')

SELECT  
    DISTINCT p1.PhraseID, 
    STUFF(
        ( 
            SELECT  
                ' ' + p2.word 
            FROM    
                @phrases AS p2 
            WHERE   
                p2.PhraseID = p1.PhraseID 
            FOR XML PATH('')
        ), 1, 1, '') AS completePhrase
FROM      
    @phrases AS p1
ORDER BY  
    p1.PhraseID

工作正常,但如果示例使用的字符在XML中使用时需要转义,则会出现问题。例如,通过它运行以下数据:

insert 
    @words 
values
    (1,1,1,'hello>'), --notice the less than symbol
    (2,1,2,'world'),
    (3,2,1,'black')

给予

hello> world

如果源表被声明为无序,则 order by 是必需的

对原始查询的一个小修改修复了所有问题:

SELECT  
    DISTINCT p1.PhraseID, 
    STUFF(
        ( 
            SELECT  
                ' ' + p2.word 
            FROM    
                @words AS p2 
            WHERE   
                p2.PhraseID = p1.PhraseID 
            ORDER BY
                p2.seqNum  --required
            FOR XML PATH(''),TYPE
        ).value('.','nvarchar(4000)'), 
        1, 
        1, 
        ''
    ) AS completePhrase
FROM      
    @words AS p1
ORDER BY  
    p1.PhraseID

(见 FOR XML PATH(''): Escaping "special" characters )