T-SQL - 字符串连接 [英] T-SQL - string concatenation

问题描述

希望有人能帮忙 - 我是一个新手 SQL 黑客(而且确实很糟糕！)

Hope someone can help - I am a novice SQL hacker (and very bad at it indeed!)

我在 SQL Server 2005 TABLE 1 和 TABLE2 上有两个表:

I have two tables on SQL Server 2005 TABLE 1 and TABLE2:

表 1

COL1         COL2
1            10
2            20
3            30
4            10
4            20
5            20
6            30
7            10
7            20

TABLE2

COL1         COL2
10            A
20            B
30            C

TABLE2 中的 COL2 是 COL2 TABLE1 中数值数据的字符表示.我希望这是可以理解的?

COL2 in TABLE2 is a character representation of the numerical data in COL2 TABLE1. I hope this is understandable?

我已经研究出如何从 TABLE1 中选择 COL1 和 COL2 并将结果连接起来以显示这一点:

I have worked out how to select COL1 and COL2 from TABLE1 and concatenate the results to show this:

COL1         COL2Concat
1            10
2            20
3            30
4            10, 20
5            20
6            30
7            10, 20, 30 

使用:

SELECT  COL1,
        STUFF(( SELECT  ',' + CAST(a.COL2 AS VARCHAR(255)) AS [text()]
                FROM    TABLE1 a
                WHERE   a.COL1 = b.COL1
                ORDER BY a.COL2
              FOR
                XML PATH('')
              ), 1, 1, '') AS COL2Concat
FROM    TABLE1 b
GROUP BY COL1
ORDER BY COL1

但现在我想尝试获得相同的结果，除了使用 COL2 TABLE2 中的数据......即:

But now I'd like to try and get the same result except use the data in COL2 TABLE2... i.e.:

COL1         COL2Concat
1            A
2            B
3            C
4            A, B
5            B
6            C
7            A, B, C 

任何想法 - 我一直坚持说实话，因为我已经尝试修改 STUFF 查询，但它似乎永远不会出现......

Any ideas - I'm stuck to be honest as I have tried modifying the STUFF query, but it never seems to come out right...

推荐答案

你可以试试...

SELECT  COL1,
        STUFF(( SELECT  ',' + CAST((SELECT COL2
                                        FROM TABLE2
                                        WHERE TABLE2.COL1 = a.COL1) AS VARCHAR(255)) AS [text()]
                FROM    TABLE1 a
                WHERE   a.COL1 = b.COL1
                ORDER BY a.COL2
              FOR
                XML PATH('')
              ), 1, 1, '') AS COL2Concat
FROM    TABLE1 b
GROUP BY COL1
ORDER BY COL1

这篇关于T-SQL - 字符串连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！