sql - 带有父数据和子数据的 Microsoft SQL 联合

Question

期望的结果我希望有人能引导我走向正确的方向。我正在使用 Microsoft SQL，我需要返回一个父行，其下有子行，但我需要为表中的每个人执行此操作。我试过一个游标，他们会给我想要的结果，但它会在单独的结果窗口中返回每个结果。最重要的是，我需要能够将其他数据联合到父母和孩子。说完所有数据后，所有数据都将在一个 SQL 查询集中返回。

Select user_name from test (This is a header)
Union All
Select Parent_name, Parent_DOB, Parent_Phone from Parents

现在我想找到属于上面父级的任何子级，并将它们列在父级下方，每个子级都在自己的行中。

然后为表中的每个父级重复父子组合。

然后在列出所有父母和孩子之后，我需要合并一些页脚

Union All 从 blah 中选择 blah blah（这是页脚）

这是代码：

Declare @SequentialRun varchar(5)
set @SequentialRun = 19999
Declare @PayID varchar(10)
Declare @PTDBWGross as nvarchar(50)
Declare @PTDMOGross as nvarchar(50)
set @PayID = '19361*BW';     -- Need to find a better way to do this
Declare @PayMOID varchar(10)
set @PayMOID = '19373*MO';     -- Need to find a better way to do this
    set @PTDBWGross = (
SELECT SUM(PTD_GROSS)  -
(Select sum(PTD_BD_EMPLYE_CALC_AMTS) 
FROM PTD WHERE PTD_BD_CODES IN ('D1EE','H1EC','R3%M','V1EC')
AND Left(PAYTODAT_ID,8) = @PayID) FROM PAYTODAT
WHERE Left(PAYTODAT_ID,8) = @PayID

); -- Current Taxable Wages

        set @PTDMOGross = (
SELECT SUM(PTD_GROSS)  -
(Select sum(PTD_BD_EMPLYE_CALC_AMTS) 
FROM PTDBNDED WHERE PTD_BD_CODES IN ('D1EE','H1EC','R3%M','V1EC')
AND Left(PAYTODAT_ID,8) = @PayMOID) FROM PAYTODAT
WHERE Left(PAYTODAT_ID,8) = @PaymoID); -- Current Taxable Wages


WITH Xheader AS
(
Select 'X' --Record Identifier plus blank
+' '+
+ 'T3.03' -- File format version
+'I' -- Interim
+'2021' -- year for tax data
+'1' -- quarter for tax data
+ replace(replace(convert(varchar, getdate(),112),'/',''),'-','') --Creation Date
+ replace(replace(convert(varchar, getdate(),8),':',''),'-','') -- Creation Time
+ CAST('Some University' as CHAR(50))      --Extract Program Information    
+space(10)
+' 1'
+space(70)
+ CAST('System' as CHAR(25))
+ CAST('5.16' as CHAR(25))  
+'InterfaceVersio'
+CHAR(13)+CHAR(10) as TaxData,'' AS SPC,'' AS SPC1,''AS SPC2,''AS SPC3,''AS SPC4,''AS SPC5,''AS SPC6,''AS SPC7,''AS SPC8,''AS SPC9,''AS SPC10,''AS SPC11,''AS SPC12
),


HHEADER AS
(
Select 'H' --Record Identifier plus blank
+ ' ' -- File format version
+ CAST('2NI' as CHAR(14))
+' '
+CAST('Some University' as CHAR(50))
+CHAR(13)+CHAR(10) AS HHEADER,'' AS SPC,'' AS SPC1,''AS SPC2,''AS SPC3,''AS SPC4,''AS SPC5,''AS SPC6,''AS SPC7,''AS SPC8,''AS SPC9,''AS SPC10,''AS SPC11,''AS SPC12
),

RecordE as 
(
Select 'E' AS RECORDID
,SPACE(7) AS SPACES7
,P.ID AS ID
, ISNULL(REPLACE(SSN,'-',''),SPACE(9)) AS SSN
,CAST(P.FIRST_NAME AS VARCHAR(17)) AS FIRST_NAME
,ISNULL(LEFT(P.MIDDLE_NAME,1),' ') AS MIDDLE_NAME
,SPACE(16) AS SPACES16
,P.LAST_NAME AS LAST_NAME
,ADDRESS AS ADDRESS
,ISNULL(ADDRESS2,SPACE(30)) AS ADDRESS2
,ISNULL(PV.CITY,SPACE(30)) AS CITY
,ISNULL(PV.STATE,SPACE(2)) AS STATE
,ISNULL(REPLACE(PV.ZIP,'-',''),SPACE(9)) AS ZIP
,SPACE(96) 
+CHAR(13)
+CHAR(10) AS SPACES96ANDCRLF
from TEST P
INNER JOIN TEST2 PV ON PV.ID = P.ID
WHERE P.LAST_NAME IS NOT NULL AND SSN IS NOT NULL

-----I need the information below to be returned underneath each person above that is returned------------------

--(SELECT ISNULL(dbo.PadLeft(SUM(PTD_EMPLYE_TAX_AMTS),16), dbo.PadLeft(0,16)) 
--,'' AS SPC,'' AS SPC1,''AS SPC2,''AS SPC3,''AS SPC4,''AS SPC5,''AS SPC6,''AS SPC7,''AS SPC8,''AS SPC9,''AS SPC10,''AS SPC11,''AS SPC12
--FROM [TAXES] PTAX where PTAX.PTD_TAX_CODES = 'CC02' AND Left(PTAX.PAYTODAT_ID,8) = '19373*MO')

--person
--person data
--person
--person data

) 

Select * from Xheader
Union ALL
Select * from HHEADER
Union ALL
Select * from RecordE

标签： sqlsql-server