Data Roll Up

Other SQL Server Topics
1

I have a task to roll up a test sample.
id,age,parentid
1,56,0
2,23,1
3,18,1
4,76,0
5,34,4
6,51,4
7,43,4
8,43,0
9,19,8
10,17,8
11,16,8

Result
parentid,youngest,eldest
1,3,2
4,5,6
8,11,9

I have the script to set the parent ID, but the crating the results I am stuck on. Can anyone give pointers or help?

This is how I set the parent ID
SELECT C.aid as ID,c.aage as age, min(D.parentid) as parentid
INTO #Test2_Output
FROM Scott_Work.dbo.Test2 as D
JOIN
--This section pulls of the linked ID's into a group(C) based on what matched (parentid)
(SELECT A.ID as aid, B.ID as bid,a.age as aage FROM Scott_Work.dbo.Test2 as A JOIN Scott_Work.dbo.Test2 as B ON A.parentid = B.parentid) AS C
ON C.bid = D.ID
group by C.aid,c.aage order by 1

2

Welcome to this forum!

It's really helpful for responders if you present data in a usable format, like this:

CREATE TABLE #data (
    id int not null,
    age int not null,
    parentid int not null
    )
insert into #data values
(1,56,0),
(2,23,1),
(3,18,1),
(4,76,0),
(5,34,4),
(6,51,4),
(7,43,4),
(8,43,0),
(9,19,8),
(10,17,8),
(11,16,8)

That makes it much easier for us to write code.

Assuming you are on SQL 2012 or later EDIT: this code should work for SQL 2005 up, I changed my coding approach in the process, the code below should do it. If it runs too slowly, let me know and we can try to speed it up. I added a check in case there is only one child entry, to prevent listing that one id for both youngest and eldest -- if you don't want that, naturally just move the check:

SELECT d_parent.id AS parentid,
    youngest.id AS youngest,
    CASE WHEN eldest.id = youngest.id THEN NULL ELSE eldest.id END AS eldest    
FROM #data d_parent
OUTER APPLY (
    SELECT TOP (1) d_child.*
    FROM #data d_child
    WHERE d_child.parentid = d_parent.id
    ORDER BY d_child.age
) AS youngest
OUTER APPLY (
    SELECT TOP (1) d_child.*
    FROM #data d_child
    WHERE d_child.parentid = d_parent.id
    ORDER BY d_child.age DESC
) AS eldest
WHERE d_parent.parentid = 0
3

Thank you for the coding.

I will enter the data as requested in the future.

Is there a place I can get information on more complex SQL training? I have done this for years, but not to this depth.

4

I've found the SQL Server Stairways series to be pretty good. You can proceed at your own pace and pick from among many topics.