将多行折叠成一行以进行导出 [英] Collapsing Multiple Rows to One Line for Export

问题描述

我已经尝试了一段时间，(我是SQL SERVER中的新手)

I''ve been trying to do this for a while, (I''m new in SQL SERVER)

Table 1
ID Parents Name Child ID 
1  JOHN            1
1  JOHN            2
2  SARA            3
3  EVAN            4

Table 2
ID Child Name Parents ID
1  JOHNNY          1
2  SCOTT           1
3  SOPHIA          2
4  ROSE            3

我希望约翰的孩子像这样单排出现

I want the children of JOHN to appear with him in a single row like this

Table Results
ID Parents Name Child Name
1  JOHN            JOHNNY SCOTT
2  SARA            SOPHIA
3  EVAN            ROSE

使用游标是可行的，但要以性能为代价.
还有其他方法可以在不影响性能的情况下进行此操作吗?
这就是为什么我需要在没有游标的情况下执行此操作的原因:

Using the cursor worked, but at the cost of performance.
Is there any other way to do this without the need to compromise performance?
Here''s why i need to perform this without cursors:

Table 1
ID Parents Name Child ID
1  JOHN            1
1  JOHN            2
2  SARA            3
3  EVAN            4
Table 2
ID Child Name Parents ID
1  JOHNNY          1
2  SCOTT           1
3  SOPHIA          2
4  ROSE            3
Table 3
ID Service    Child ID
1  DAYTIME       1
2  NIGHTTIME     1
1  DAYTIME       2
1  DAYTIME       3
2  NIGHTTIME     4

Table Results should be   

ID Parents Name    Child Name1 Service1  Service 2    Child Name2 Service
1  JOHN            JOHNNY      DAYTIME   NIGHTTIME    SCOTT       DAYTIME
2  SARA            SOPHIA      DAYTIME
3  EVAN            ROSE        NIGHTTIME

同样，列也没有必要，因为这是一个平面文件输出.

每个父母都应该有一个孩子，但是一个父母可以生一个以上的孩子.每个孩子都应该获得一项服务，但是一个孩子可以获得一项以上的服务.游标选项可以解决问题，但是要花费超过50％的CPU和30分钟的查询.

Again columns are not necessary because this is a flatfile output.

Each parent should have a child, but a parent could have more than 1 child. Each child should have a service but a child could have more than 1 service. The cursor option do the trick but at the cost of more than 50% of CPU and 30 mins query.

推荐答案

您需要一个函数来完成此任务.我为你写了一个简单的脚本.

You need a function to accomplish this. I wrote a simple script for you.

CREATE FUNCTION [dbo].[GetChildName](@parentId INT)
RETURNS VARCHAR(8000) AS
BEGIN
    DECLARE @ChildName AS VARCHAR(10)
    DECLARE @Names AS VARCHAR(8000)
    SET @Names = ''
    DECLARE TempCur CURSOR FOR
    SELECT [Name] FROM Table2 WHERE ParentId = @parentId

    OPEN TempCur

    FETCH TempCur INTO @ChildName
    WHILE @@fetch_status = 0
    BEGIN
        SET @Names = @Names + ' ' + @ChildName
        FETCH TempCur INTO @ChildName
    END
    CLOSE TempCur
    DEALLOCATE TempCur
    RETURN @Names
END

创建函数后，将其用作选择.

after you create the function, use this as your select.

SELECT ID, ParentsName, dbo.GetChildName(ID) AS 'ChildName'
FROM Table1

希望对您有所帮助.

Hope this helps.

请参阅此链接可能对您有所帮助
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150558 [ ^ ]

Please see this link it may help u
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150558[^]

我设法解决了这个问题，这要归功于我刚刚找到了寻找另一种方法的文章，这是对我有帮助的代码块:

Hi guys i managed to deal with this thanks to an article i just found searching for another method, here''s the block that helped me:

SELECT p1.CategoryId,
       ( SELECT ProductName + ','
           FROM Northwind.dbo.Products p2
          WHERE p2.CategoryId = p1.CategoryId
          ORDER BY ProductName
            FOR XML PATH('') ) AS Products
      FROM Northwind.dbo.Products p1
      GROUP BY CategoryId ;

这是完整的文章: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ [

Here''s the complete article: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[^]
By the way from 30 minutes of query down to 8 minutes and produced the same results! :) Hope this helps!

这篇关于将多行折叠成一行以进行导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！