Transpose Rows
-
Hi, I'm using a recursive CTE to build a family tree. All works fine but instead of having a row for each person in the family tree, I want one row as the result with each constituent person as a column. How do I do this? I will have the same maximum number of generations in the result-set (not necessarily the same number of results because some may be missing)
Here's what I have:
CREATE TABLE Person(
ID int NOT NULL
,PersonName varchar(50)
,FemParent int
,MaleParent int
)
INSERT INTO Person(ID, PersonName, FemParent, MaleParent)
VALUES(1, 'FRED', 2, 3)
INSERT INTO Person(ID, PersonName, FemParent, MaleParent)
VALUES(2, 'Mary', 4, 5)
INSERT INTO Person(ID, PersonName, FemParent, MaleParent)
VALUES(3, 'John', 6, 7)
INSERT INTO Person(ID, PersonName, FemParent, MaleParent)
VALUES(4, 'Alice', 8, 9)
INSERT INTO Person(ID, PersonName, FemParent, MaleParent)
VALUES(5, 'Bob', 10, 11)
INSERT INTO Person(ID, PersonName, FemParent, MaleParent)
VALUES(6, 'Wendy', 12, 13)
INSERT INTO Person(ID, PersonName, FemParent, MaleParent)
VALUES(7, 'James', 14, 15)
WITH ParentInfo (ID, FemParent, MaleParent, PersonName, GenName, Generation) AS
(
SELECTID
,FemParent
,MaleParent
,PersonName
,CAST('' as varchar(max))
,0 AS Generation
FROMPerson
WHEREID = 1
UNION ALL
SELECTFParent.ID
,FParent.FemParent
,FParent.MaleParent
,FParent.PersonName
,CAST(ISNULL('Mother', '') + ParentInfo.GenName as varchar(max)) --CAST(ISNULL(an.PedPos,'') as varchar(50)) + 'Dam'
,ParentInfo.Generation - 1
FROMPerson FParent
INNER JOIN ParentInfo ON
FParent.ID = ParentInfo.FemParent
UNION ALL
SELECTFParent.ID
,FParent.FemParent
,FParent.MaleParent
,FParent.PersonName
,CAST(ISNULL('Father', '') + ParentInfo.GenName as varchar(max)) --CAST(ISNULL(an.PedPos,'') as varchar(50)) + 'Dam'
,ParentInfo.Generation - 1
FROMPerson FParent
INNER JOIN ParentInfo ON
FParent.ID = ParentInfo.MaleParent
)
SELECT * FROM ParentInfoThis gives an output with a row for each parent/ grandparent etc. as shown in FamilyTree.jpg
I want my output to be in the form shown in FamilyTree2 which has one row for all of the results.
Any smart (or otherwise!) way to do this?
Thanks
- This topic was modified 4 years, 2 months ago by dec_obrien. Reason: Missing attachments
-
Maybe this, assumes a fixed number of generations
WITH CTE AS (
SELECT p1.ID AS RootID, p1.PersonName AS RootName, p1.ID, p1.PersonName, p1.FemParent, p1.MaleParent,
CAST('' AS VARCHAR(1000)) AS Rel
FROM Person p1
WHERE NOT EXISTS(SELECT * FROM Person p2 WHERE p1.ID IN (p2.FemParent, p2.MaleParent))
UNION ALL
SELECT c.RootID, c.RootName, p.ID, p.PersonName, p.FemParent, p.MaleParent,
CAST(CASE WHEN p.ID = c.MaleParent THEN 'Father' ELSE 'Mother' END + c.Rel AS VARCHAR(1000))
FROM CTE c
INNER JOIN Person p ON p.ID IN (c.FemParent, c.MaleParent)
)
SELECT RootID AS ID,
RootName AS PersonName,
MAX(CASE WHEN Rel='Mother' THEN PersonName END) AS Mother,
MAX(CASE WHEN Rel='Father' THEN PersonName END) AS Father,
MAX(CASE WHEN Rel='MotherFather' THEN PersonName END) AS MotherFather,
MAX(CASE WHEN Rel='FatherFather' THEN PersonName END) AS FatherFather,
MAX(CASE WHEN Rel='MotherMother' THEN PersonName END) AS MotherMother,
MAX(CASE WHEN Rel='FatherMother' THEN PersonName END) AS FatherMother
FROM CTE
GROUP BY RootID, RootName;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi, thanks for taking the time to reply. Yes, it works and is a lot more elegant (and probably more efficient!) than my way using a cursor. This was my way:
DECLARE @FemP varchar(max)
DECLARE @MaleP varchar(max)
DECLARE @GenName varchar(max)
DECLARE @PersonName varchar(max)
DECLARE @ID int
DECLARE @Trans CURSOR;
SET @Trans = CURSOR FOR WITH ParentInfo (ID, FemParent, MaleParent, PersonName, GenName, Generation) AS
(
SELECTID
,FemParent
,MaleParent
,PersonName
,CAST('' as varchar(max))
,0 AS Generation
FROMPerson
WHEREID = 1
UNION ALL
SELECTFParent.ID
,FParent.FemParent
,FParent.MaleParent
,FParent.PersonName
,CAST(ISNULL('Mother', '') + ParentInfo.GenName as varchar(max)) --CAST(ISNULL(an.PedPos,'') as varchar(50)) + 'Dam'
,ParentInfo.Generation - 1
FROMPerson FParent
INNER JOIN ParentInfo ON
FParent.ID = ParentInfo.FemParent
UNION ALL
SELECTFParent.ID
,FParent.FemParent
,FParent.MaleParent
,FParent.PersonName
,CAST(ISNULL('Father', '') + ParentInfo.GenName as varchar(max)) --CAST(ISNULL(an.PedPos,'') as varchar(50)) + 'Dam'
,ParentInfo.Generation - 1
FROMPerson FParent
INNER JOIN ParentInfo ON
FParent.ID = ParentInfo.MaleParent
)
SELECT PersonName, FemParent, MaleParent, GenName FROM ParentInfo;
OPEN @Trans;
CREATE TABLE #Pedigree(
id int
,PersonName varchar(50)
,Mother varchar(50)
,Father varchar(50)
,MotherMother varchar(50)
,MotherFather varchar(50)
,FatherMother varchar(50)
,FatherFather varchar(50)
)
DECLARE @sql nvarchar(Max)
FETCH NEXT FROM @Trans INTO
@PersonName
,@FemP
,@MaleP
,@GenName
SET @PersonName = '''' + @PersonName + ''''
SET @sql = 'INSERT INTO #Pedigree(id, PersonName) VALUES (1, ' + @PersonName + ')'
Exec sp_executesql @sql
WHILE @@FETCH_STATUS = 0
BEGIN
IF ISNULL(@GenName, '') <>''
BEGIN
SET @PersonName = '''' + @PersonName + ''''
set @sql = ('UPDATE #Pedigree SET ' + @GenName + ' = ' + @PersonName + ' WHERE id = 1')
--print @sql
Exec sp_executesql @sql
END
FETCH NEXT FROM @Trans INTO
@PersonName
,@FemP
,@MaleP
,@GenName;
END;
CLOSE @Trans;
SELECT * from #Pedigree
DROP TABLE #Pedigree -
Hi Mark,
I just noticed a small error in your code so I'll correct it on the off chance somebody ever needs something like this again!
This line should be the other way around (it matters when you get to more generations:
Using standard nomenclature, you great grandmother ( your Dad's Dad's Mum) would be FatherFatherMother
CAST(CASE WHEN p.ID = c.MaleParent THEN 'Father' ELSE 'Mother' END + c.Rel AS VARCHAR(1000))
should read
CAST(c.Rel + CASE WHEN p.ID = c.MaleParent THEN 'Father' ELSE 'Mother' END AS VARCHAR(1000)) -
Incentive trying to mimic procedural (recursive) code in SQL, I would've used a nested set model. You can Google it and see that it's very set oriented so it works well in this language. However, if you're serious about doing genealogy, have you thought about going to the Mormons and getting some of their software?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply