SQL SQL选择一列中的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14065277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL select multiple rows in one column
提问by Gulrej
I have table TestTable
我有表TestTable
ID Name
-------
1 A
1 B
1 C
2 D
2 E
3 F
I want to write a query in SQL Server 2008 which will return
我想在 SQL Server 2008 中编写一个将返回的查询
ID Name
----------
1 A,B,C
2 D,E
3 F
Please someone help me to write this query.
请有人帮我写这个查询。
回答by Mahmoud Gamal
AFAIK, there is no native way to do so. However, you can use the FOR XMLto do this like so:
AFAIK，没有本地方法可以这样做。但是，您可以使用FOR XML来执行此操作：
SELECT
t1.Id,
STUFF((
SELECT ', ' + t2.name
FROM Table1 t2
WHERE t2.ID = t1.ID
FOR XML PATH (''))
,1,2,'') AS Names
FROM Table1 t1
GROUP BY t1.Id;
SQL Fiddle Demo
SQL 小提琴演示
This will give you:
这会给你：
| ID | NAMES |
----------------
| 1 | A, B, C |
| 2 | D, E |
| 3 | F |
回答by Ajith Sasidharan
try this ::
尝试这个 ：：
SELECT a.ID,
SUBSTRING(d.Name,1, LEN(d.Name) - 1) Name
FROM
(
SELECT DISTINCT ID
FROM testTable
) a
CROSS APPLY
(
SELECT [Name] + ', '
FROM testTable AS B
WHERE A.ID = B.ID
FOR XML PATH('')
) D (Name)
回答by Melvin
SQL server 2008supports this by using Pivot or Unpivot@ http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
SQL Server 2008通过使用Pivot or Unpivot@ http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx支持这一点
