Customer Table -------------- ID Name 1 James 2 Peter
Order Table --------------- OrderId CustId 100 1 101 1 102 2
How can I write a query that returns something like this
如何编写一个返回如下内容的查询呢
ID,Name,ListofOrders 1,James,"100,101" 2,Peter,"102"
In Sybase I had a function called LIST which I could use but I dont find a similar function in SQL SERVER
在Sybase中，我有一个名为LIST的函数，我可以使用它，但我在SQL SERVER中找不到类似的函数
4 个解决方案
#1
12
Please try:
请尝试:
select ID, [Name],
(select OrderID+',' from OrderTable where CustID=ID
group by OrderID for xml path('')) AS ListOfOrders
From CustomerTable
#2
1
Create a User Defined Function as shown below
创建一个用户定义的函数，如下所示
CREATE FUNCTION [dbo].[CommaSeperatedOrderIDs](@CustId INT) returns varchar(Max)
AS
BEGIN
DECLARE @CommaSeperatedValues VARCHAR(MAX)
SELECT @CommaSeperatedValues = COALESCE(@CommaSeperatedValues+',' , '') + OrderID
FROM OrderTable WHERE CustId = @CustId
RETURN @CommaSeperatedValues
END
And then,
然后,
select ID, [Name], ([dbo].[CommaSeperatedOrderIDs](ID)) AS ListofOrders
From CustomerTable
#3
0
A very simple and handy solution given on the link below.
下面的链接给出了一个非常简单和方便的解决方案。
http://tejasnshah.wordpress.com/2009/02/28/sql-server-get-column-values-as-comma-seperated-list-using-xml-path-instead-of-udfs-using-sql-coalesce/
The SQL query written on that link is in an image ...so i couldn't copy it here.
在这个链接上写的SQL查询在一个图像中…所以我不能在这里复制。
#4
0
Adding full details from Sheikh Haris' link.
从谢赫·哈里斯的链接中添加详细信息。
Given this table:
鉴于这种表:
To get output like:
得到输出:
Use the following SQL:
使用以下SQL:
SELECT field1,
Substring(convert(varchar(100),
(
SELECT (', ' + field2)
FROM #test t2
WHERE t1.field1 = t2.field1
ORDER BY field1, field2
FOR XML PATH( '' )
)), 3, 1000 )
FROM #test t1
GROUP BY field1
I added a convert function to the substring so that the WIDEMEMO field would be displayed (SQL Server)
我向子字符串添加了一个convert函数，以便显示WIDEMEMO字段(SQL Server)
#1
12
Please try:
请尝试:
select ID, [Name],
(select OrderID+',' from OrderTable where CustID=ID
group by OrderID for xml path('')) AS ListOfOrders
From CustomerTable
#2
1
Create a User Defined Function as shown below
创建一个用户定义的函数，如下所示
CREATE FUNCTION [dbo].[CommaSeperatedOrderIDs](@CustId INT) returns varchar(Max)
AS
BEGIN
DECLARE @CommaSeperatedValues VARCHAR(MAX)
SELECT @CommaSeperatedValues = COALESCE(@CommaSeperatedValues+',' , '') + OrderID
FROM OrderTable WHERE CustId = @CustId
RETURN @CommaSeperatedValues
END
And then,
然后,
select ID, [Name], ([dbo].[CommaSeperatedOrderIDs](ID)) AS ListofOrders
From CustomerTable
#3
0
A very simple and handy solution given on the link below.
下面的链接给出了一个非常简单和方便的解决方案。
http://tejasnshah.wordpress.com/2009/02/28/sql-server-get-column-values-as-comma-seperated-list-using-xml-path-instead-of-udfs-using-sql-coalesce/
The SQL query written on that link is in an image ...so i couldn't copy it here.
在这个链接上写的SQL查询在一个图像中…所以我不能在这里复制。
#4
0
Adding full details from Sheikh Haris' link.
从谢赫·哈里斯的链接中添加详细信息。
Given this table:
鉴于这种表:
To get output like:
得到输出:
Use the following SQL:
使用以下SQL:
SELECT field1,
Substring(convert(varchar(100),
(
SELECT (', ' + field2)
FROM #test t2
WHERE t1.field1 = t2.field1
ORDER BY field1, field2
FOR XML PATH( '' )
)), 3, 1000 )
FROM #test t1
GROUP BY field1
I added a convert function to the substring so that the WIDEMEMO field would be displayed (SQL Server)
我向子字符串添加了一个convert函数，以便显示WIDEMEMO字段(SQL Server)