Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
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


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.
Posted
Updated 24-Mar-11 6:07am
v8
Comments
walterhevedeich 17-Mar-11 16:46pm    
I have some clarifications. What do you want the output to be if incase this will be the data?
Table
CLIENT NAME CHILD
1 JOHN JOHNNY
1 JOHN SCOTT
1 JOHN LONG
2 SARA SOPHIA
3 EVAN ROSE
Sunasara Imdadhusen 18-Mar-11 0:56am    
Every client's comes with it's child? Please confirm!!
Dalek Dave 23-Mar-11 12:11pm    
Edited for Grammar and Readability.

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

SQL
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.

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


Hope this helps.
 
Share this answer
 
v2
Comments
rhapemerald 22-Mar-11 10:28am    
It worked thanks!
rhapemerald 23-Mar-11 11:42am    
Is there any other way to do this without the need of cursor?
walterhevedeich 25-Mar-11 18:21pm    
none that I know of
Dalek Dave 23-Mar-11 12:15pm    
Good Answer.
Please see this link it may help u
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150558[^]
 
Share this answer
 
Comments
Dalek Dave 23-Mar-11 12:15pm    
Good Link
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:

SQL
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 ;

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!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900