SQL Server:在输出中合并单个表中的数据行

Question

我有一个SQL Server表，其中包含以下字段和示例数据:

ID         Name   Address   Age
23052-PF   Peter  Timbuktu  25
23052-D1   Jane   Paris     22
23052-D2   David  London    24
23050-PF   Sam    Beijing   22
23051-PF   Nancy  NYC       26
23051-D1   Carson Cali      22
23056-PF   Grace  LA        28
23056-D1   Smith  Boston    23
23056-D2   Mark   Adelaide  26
23056-D3   Hose   Mexico    25
23056-D4   Mandy  Victoria  24

每个带-PF的ID在表中是唯一的。

每个带-Dx的ID都与带-PF的ID相关联。

每个带-PF的ID可以有0个或多个带-Dx的ID。

对于给定的-PF， -Dx的最大行数是9。

。一个ID 11111-PF可以有11111-D1, 11111-D2, 11111-D3直到11111-D9。

上述示例数据的预期输出:

ID        ID (without suffix)  PF_Name  PF_Address  PF_Age  D_Name   D_Address   D_Age
23052-PF  23052                Peter    Timbuktu    25      Jane     Paris       22
23052-PF  23052                Peter    Timbuktu    25      David    London      24
23050-PF  23050                Sam      Beijing     22      NULL     NULL        NULL
23051-PF  23051                Nancy    NYC         26      Carson   Cali        22
23056-PF  23056                Grace    LA          28      Smith    Boston      23
23056-PF  23056                Grace    LA          28      Mark     Adelaide    26
23056-PF  23056                Grace    LA          28      Hose     Mexico      25
23056-PF  23056                Grace    LA          28      Mandy    Victoria    24

我需要能够像上面那样连接-PF和-Dx。

如果-PF有0 Dx行，那么输出中的D_Name, D_Address和D_Age列应该返回NULL。

如果-PF有一个或多个Dx行，那么PF_Name, PF_Address和PF_Age应该在输出的每一行重复，D_Name, D_Address和D_Age应该包含每个相关Dx行的值。

需要使用MSSQL

查询不应该使用视图或创建额外的表。

谢谢你的帮助!

Answer 1

select 
    pf.ID,
    pf.IDNum,
    pf.Name as PF_Name,
    pf.Address as PF_Address,
    pf.Age as PF_Age,
    dx.Name as D_Name,
    dx.Address as D_Address,
    dx.Age as D_Age
from
(
select
    ID, left(ID, 5) as IDNum, Name, Address, Age
from
    mytable
where
    right(ID, 3) = '-PF'
) pf
left outer join
(
select
    ID, left(ID, 5) as IDNum, Name, Address, Age
from
    mytable
where
    right(ID, 3) != '-PF'
) dx
on pf.IDNum = dx.IDNum

SqlFiddle demo: http://sqlfiddle.com/#!6/dfdbb/1

Answer 2

SELECT t1.ID, LEFT(t1.ID,5) "ID (without Suffix)",
    t1.Name "PF_Name", t1.Address "PF_Address", t1.Age "PF_Age",
    t2.Name "D_Name", t2.Address "D_Address", t2.Age "D_Age"
FROM PFTable t1
LEFT JOIN PFTable t2 on LEFT(t1.ID,5) = LEFT(t2.ID,5)
WHERE RIGHT(t1.ID,2) = 'PF'