how to join one select statement to another pivot select statement [closed]

By Posted on

Question :

SELECT emp.UserId
    ,emp.EmployeeId
    ,emp.FirstName
    ,emp.MiddleName
    ,emp.LastName
    ,emp.MobileNumber
    ,emp.HomeNumber
    ,emp.OfficeNumber
    ,convert(VARCHAR(10), emp.USJoindate, 10) AS USJoindate
    ,convert(VARCHAR(10), emp.IndiaJoindate, 10) AS IndiaJoindate
    ,convert(VARCHAR(10), emp.BackgroundVerificationdate, 10) AS BackgroundVerificationdate
    ,eq.EducationalQualification
    ,emp.OffshoreCN1
    ,emp.OffshoreCN2
    ,emp.OffshoreCN3
    ,emp.OffshoreCN4
    ,emp.OffshoreCN5
    ,emp.OffshoreCN6
    ,emp.OffshoreCN7
    ,emp.OffshoreCN8
    ,emp.OffshoreCN9
    ,emp.OffshoreCN10
    ,emp.PersonalEmail
    ,emp.PersonalAddress
    ,emp.Zipcode
    ,emp.BloodGroup
    ,convert(VARCHAR(10), emp.DOB, 10) AS DOB
    ,emp.city
    ,emp.STATE
    ,emp.MaritalStatus
    ,emp.Photo
    ,emp.OfficialEmail
    ,j.JobTitle
    ,i.PassportNumber
    ,convert(VARCHAR(10), i.PassportStartDate, 10) AS PassportStartDate
    ,convert(VARCHAR(10), i.PassportEndDate, 10) AS PassportEndDate
    ,convert(VARCHAR(10), i.I94StartDate, 10) AS I94StartDate
    ,convert(VARCHAR(10), i.I94EndDate, 10) AS I94EndDate
    ,convert(VARCHAR(10), i.VisaStartDate, 10) AS VisaStartDate
    ,convert(VARCHAR(10), i.VisaEndDate, 10) AS VisaEndDate
    ,convert(VARCHAR(10), i.PetitionStartDate, 10) AS PetitionStartDate
    ,convert(VARCHAR(10), i.PetitionEndDate, 10) AS PetitionEndDate
    ,convert(VARCHAR(10), i.WACNumber, 10) AS WACNumber
    ,convert(VARCHAR(10), i.PetitionRenewalAppliedDate, 10) AS PetitionRenewalAppliedDate
    ,convert(VARCHAR(10), i.PetitionRFERespondedDate, 10) AS PetitionRFERespondedDate
    ,convert(VARCHAR(10), i.I140ApprovedDate, 10) AS I140ApprovedDate
    ,convert(VARCHAR(10), i.I1485ApprovedDate, 10) AS I1485ApprovedDate
    ,i.GCCategoryType
    ,it.NAME AS Immigrationtypename
    ,stuff((
            SELECT '|' + DependentName
            FROM EmployeeDependents ed
            WHERE ed.UserId = emp.Userid
            FOR XML path('')
            ), 1, 1, '') AS DependentName
    ,p.ProjectName
    ,stuff((
            SELECT ',' + Description
            FROM TimeTrackerEntryTable AS t
            WHERE t.Userid = emp.userid
            FOR XML path('')
            ), 1, 1, '') AS PDescription
    ,stuff((
            SELECT ',' + ProvidedChequeNo
            FROM LoanProvidedDetail AS lp
            WHERE lp.UserId = emp.UserId
            FOR XML path('')
            ), 1, 1, '') AS ProvidedChequeNo
    ,stuff((
            SELECT ',' + ProvidedAmount
            FROM LoanProvidedDetail AS lp
            WHERE lp.UserId = emp.UserId
            ORDER BY lp.provideddate DESC
            FOR XML path('')
            ), 10, 1, '') AS ProvidedAmount
    ,stuff((
            SELECT ',' + CurrentChequeNo
            FROM LoanPaymentDetails AS lp
            WHERE lp.UserId = emp.UserId
            ORDER BY lp.paymentdate DESC
            FOR XML path('')
            ), 1, 1, '') AS CurrentChequeNo
    ,stuff((
            SELECT ',' + ir.ChequeNo
            FROM ImmigrationRepeatableGroup AS ir
            WHERE ir.ImmigrationId = irg.ImmigrationId
            ORDER BY ir.PaymentDate DESC
            FOR XML path('')
            ), 1, 1, '') AS ImmigrationChequeNo
FROM EmployeeBasicDetails AS emp
LEFT OUTER JOIN JobtitleDDTable AS j 
    ON j.Id = emp.JobTitle
INNER JOIN EmployeeImmegrationDetails AS i 
    ON emp.UserId = i.UserId
LEFT OUTER JOIN TimeTrackerEntryTable k 
    ON i.UserId = k.UserId
LEFT OUTER JOIN ProjectDetails p 
    ON p.ProjectId = k.ProjectId
LEFT OUTER JOIN LoanProvidedDetail lpd 
    ON lpd.UserId = emp.UserId
LEFT OUTER JOIN LoanPaymentDetails lp 
    ON lp.UserId = emp.UserId
INNER JOIN EduQualificationDDTable AS eq 
    ON emp.Qualification = eq.Id
INNER JOIN ImmigrationType AS it 
    ON it.ImmigrationTypeId = i.ImmigrationStatusType
LEFT OUTER JOIN ImmigrationRepeatableGroup AS irg 
    ON irg.ImmigrationId = i.ImmigrationId
GROUP BY emp.UserId
    ,emp.EmployeeId
    ,emp.FirstName
    ,emp.MiddleName
    ,emp.LastName
    ,emp.MobileNumber
    ,emp.HomeNumber
    ,emp.OfficeNumber
    ,emp.OffshoreCN1
    ,emp.OffshoreCN2
    ,emp.OffshoreCN3
    ,emp.OffshoreCN4
    ,emp.OffshoreCN5
    ,emp.OffshoreCN6
    ,emp.OffshoreCN7
    ,emp.OffshoreCN8
    ,emp.OffshoreCN9
    ,emp.OffshoreCN10
    ,emp.PersonalEmail
    ,emp.PersonalAddress
    ,emp.Zipcode
    ,emp.BloodGroup
    ,emp.DOB
    ,emp.city
    ,emp.STATE
    ,emp.MaritalStatus
    ,emp.Photo
    ,emp.OfficialEmail
    ,j.JobTitle
    ,i.PassportNumber
    ,p.ProjectName
    ,eq.EducationalQualification
    ,USJoindate
    ,IndiaJoindate
    ,BackgroundVerificationdate
    ,PassportStartDate
    ,PassportEndDate
    ,it.NAME
    ,I94StartDate
    ,I94EndDate
    ,VisaStartDate
    ,VisaEndDate
    ,PetitionStartDate
    ,PetitionEndDate
    ,WACNumber
    ,PetitionRenewalAppliedDate
    ,PetitionRFERespondedDate
    ,I140ApprovedDate
    ,I1485ApprovedDate
    ,i.GCCategoryType
    ,irg.ImmigrationId

another pivot using select statement

SELECT Userid
    ,DependentName1
    ,DependentName2
    ,DependentName3
    ,DependentName4
    ,DependentName5
    ,DependentName6
    ,DependentName7
    ,DependentName8
    ,DependentName9
FROM (
    SELECT Userid
        ,col + cast(rn AS VARCHAR(10)) col
        ,value
    FROM (
        SELECT Userid
            ,DependentName
            ,row_number() OVER (
                PARTITION BY Userid ORDER BY DependentName
            ) rn
        FROM EmployeeDependents
    ) t
    CROSS APPLY (
        SELECT 'DependentName'
            ,DependentName
    ) c(col, value)
) src
pivot(
    max(value) 
    FOR col IN (
            DependentName1
            ,DependentName2
            ,DependentName3
            ,DependentName4
            ,DependentName5
            ,DependentName6
            ,DependentName7
            ,DependentName8
            ,DependentName9
    )
) piv;

how to join this two select statement based on userid

Answer :

Wrap your queries in CTEs, like this:

WITH FirstQuery AS (
    SELECT emp.UserId
        ,emp.EmployeeId
        ,emp.FirstName
        ,emp.MiddleName
        ,emp.LastName
        ,emp.MobileNumber
        ,emp.HomeNumber
        ,emp.OfficeNumber
        ,convert(VARCHAR(10), emp.USJoindate, 10) AS USJoindate
        ,convert(VARCHAR(10), emp.IndiaJoindate, 10) AS IndiaJoindate
        ,convert(VARCHAR(10), emp.BackgroundVerificationdate, 10) AS BackgroundVerificationdate
        ,eq.EducationalQualification
        ,emp.OffshoreCN1
        ,emp.OffshoreCN2
        ,emp.OffshoreCN3
        ,emp.OffshoreCN4
        ,emp.OffshoreCN5
        ,emp.OffshoreCN6
        ,emp.OffshoreCN7
        ,emp.OffshoreCN8
        ,emp.OffshoreCN9
        ,emp.OffshoreCN10
        ,emp.PersonalEmail
        ,emp.PersonalAddress
        ,emp.Zipcode
        ,emp.BloodGroup
        ,convert(VARCHAR(10), emp.DOB, 10) AS DOB
        ,emp.city
        ,emp.STATE
        ,emp.MaritalStatus
        ,emp.Photo
        ,emp.OfficialEmail
        ,j.JobTitle
        ,i.PassportNumber
        ,convert(VARCHAR(10), i.PassportStartDate, 10) AS PassportStartDate
        ,convert(VARCHAR(10), i.PassportEndDate, 10) AS PassportEndDate
        ,convert(VARCHAR(10), i.I94StartDate, 10) AS I94StartDate
        ,convert(VARCHAR(10), i.I94EndDate, 10) AS I94EndDate
        ,convert(VARCHAR(10), i.VisaStartDate, 10) AS VisaStartDate
        ,convert(VARCHAR(10), i.VisaEndDate, 10) AS VisaEndDate
        ,convert(VARCHAR(10), i.PetitionStartDate, 10) AS PetitionStartDate
        ,convert(VARCHAR(10), i.PetitionEndDate, 10) AS PetitionEndDate
        ,convert(VARCHAR(10), i.WACNumber, 10) AS WACNumber
        ,convert(VARCHAR(10), i.PetitionRenewalAppliedDate, 10) AS PetitionRenewalAppliedDate
        ,convert(VARCHAR(10), i.PetitionRFERespondedDate, 10) AS PetitionRFERespondedDate
        ,convert(VARCHAR(10), i.I140ApprovedDate, 10) AS I140ApprovedDate
        ,convert(VARCHAR(10), i.I1485ApprovedDate, 10) AS I1485ApprovedDate
        ,i.GCCategoryType
        ,it.NAME AS Immigrationtypename
        ,stuff((
                SELECT '|' + DependentName
                FROM EmployeeDependents ed
                WHERE ed.UserId = emp.Userid
                FOR XML path('')
                ), 1, 1, '') AS DependentName
        ,p.ProjectName
        ,stuff((
                SELECT ',' + Description
                FROM TimeTrackerEntryTable AS t
                WHERE t.Userid = emp.userid
                FOR XML path('')
                ), 1, 1, '') AS PDescription
        ,stuff((
                SELECT ',' + ProvidedChequeNo
                FROM LoanProvidedDetail AS lp
                WHERE lp.UserId = emp.UserId
                FOR XML path('')
                ), 1, 1, '') AS ProvidedChequeNo
        ,stuff((
                SELECT ',' + ProvidedAmount
                FROM LoanProvidedDetail AS lp
                WHERE lp.UserId = emp.UserId
                ORDER BY lp.provideddate DESC
                FOR XML path('')
                ), 10, 1, '') AS ProvidedAmount
        ,stuff((
                SELECT ',' + CurrentChequeNo
                FROM LoanPaymentDetails AS lp
                WHERE lp.UserId = emp.UserId
                ORDER BY lp.paymentdate DESC
                FOR XML path('')
                ), 1, 1, '') AS CurrentChequeNo
        ,stuff((
                SELECT ',' + ir.ChequeNo
                FROM ImmigrationRepeatableGroup AS ir
                WHERE ir.ImmigrationId = irg.ImmigrationId
                ORDER BY ir.PaymentDate DESC
                FOR XML path('')
                ), 1, 1, '') AS ImmigrationChequeNo
    FROM EmployeeBasicDetails AS emp
    LEFT OUTER JOIN JobtitleDDTable AS j 
        ON j.Id = emp.JobTitle
    INNER JOIN EmployeeImmegrationDetails AS i 
        ON emp.UserId = i.UserId
    LEFT OUTER JOIN TimeTrackerEntryTable k 
        ON i.UserId = k.UserId
    LEFT OUTER JOIN ProjectDetails p 
        ON p.ProjectId = k.ProjectId
    LEFT OUTER JOIN LoanProvidedDetail lpd 
        ON lpd.UserId = emp.UserId
    LEFT OUTER JOIN LoanPaymentDetails lp 
        ON lp.UserId = emp.UserId
    INNER JOIN EduQualificationDDTable AS eq 
        ON emp.Qualification = eq.Id
    INNER JOIN ImmigrationType AS it 
        ON it.ImmigrationTypeId = i.ImmigrationStatusType
    LEFT OUTER JOIN ImmigrationRepeatableGroup AS irg 
        ON irg.ImmigrationId = i.ImmigrationId
    GROUP BY emp.UserId
        ,emp.EmployeeId
        ,emp.FirstName
        ,emp.MiddleName
        ,emp.LastName
        ,emp.MobileNumber
        ,emp.HomeNumber
        ,emp.OfficeNumber
        ,emp.OffshoreCN1
        ,emp.OffshoreCN2
        ,emp.OffshoreCN3
        ,emp.OffshoreCN4
        ,emp.OffshoreCN5
        ,emp.OffshoreCN6
        ,emp.OffshoreCN7
        ,emp.OffshoreCN8
        ,emp.OffshoreCN9
        ,emp.OffshoreCN10
        ,emp.PersonalEmail
        ,emp.PersonalAddress
        ,emp.Zipcode
        ,emp.BloodGroup
        ,emp.DOB
        ,emp.city
        ,emp.STATE
        ,emp.MaritalStatus
        ,emp.Photo
        ,emp.OfficialEmail
        ,j.JobTitle
        ,i.PassportNumber
        ,p.ProjectName
        ,eq.EducationalQualification
        ,USJoindate
        ,IndiaJoindate
        ,BackgroundVerificationdate
        ,PassportStartDate
        ,PassportEndDate
        ,it.NAME
        ,I94StartDate
        ,I94EndDate
        ,VisaStartDate
        ,VisaEndDate
        ,PetitionStartDate
        ,PetitionEndDate
        ,WACNumber
        ,PetitionRenewalAppliedDate
        ,PetitionRFERespondedDate
        ,I140ApprovedDate
        ,I1485ApprovedDate
        ,i.GCCategoryType
        ,irg.ImmigrationId
),
SecondQuery AS (
    SELECT Userid
        ,DependentName1
        ,DependentName2
        ,DependentName3
        ,DependentName4
        ,DependentName5
        ,DependentName6
        ,DependentName7
        ,DependentName8
        ,DependentName9
    FROM (
        SELECT Userid
            ,col + cast(rn AS VARCHAR(10)) col
            ,value
        FROM (
            SELECT Userid
                ,DependentName
                ,row_number() OVER (
                    PARTITION BY Userid ORDER BY DependentName
                ) rn
            FROM EmployeeDependents
        ) t
        CROSS APPLY (
            SELECT 'DependentName'
                ,DependentName
        ) c(col, value)
    ) src
    pivot(
        max(value) 
        FOR col IN (
                DependentName1
                ,DependentName2
                ,DependentName3
                ,DependentName4
                ,DependentName5
                ,DependentName6
                ,DependentName7
                ,DependentName8
                ,DependentName9
        )
    ) piv
)
SELECT *
FROM FirstQuery AS F
INNER JOIN SecondQuery AS S
    ON F.Userid = S.UserId

Try:

With q1 as
(
    select
        emp.UserId,
        emp.EmployeeId,
        emp.FirstName,
        emp.MiddleName,
        emp.LastName,
        emp.MobileNumber,
        emp.HomeNumber, 
        emp.OfficeNumber,
        convert(varchar(10),emp.USJoindate,10) as USJoindate,
        convert(varchar(10),emp.IndiaJoindate,10) as IndiaJoindate,
        convert(varchar(10),emp.BackgroundVerificationdate,10) as BackgroundVerificationdate,
        eq.EducationalQualification, emp.OffshoreCN1,
        emp.OffshoreCN2,
        emp.OffshoreCN3,
        emp.OffshoreCN4,
        emp.OffshoreCN5,
        emp.OffshoreCN6,
        emp.OffshoreCN7,
        emp.OffshoreCN8,
        emp.OffshoreCN9,
        emp.OffshoreCN10,
        emp.PersonalEmail,
        emp.PersonalAddress,
        emp.Zipcode, 
        emp.BloodGroup,
        convert(varchar(10),emp.DOB,10) as DOB,
        emp.city,
        emp.State,
        emp.MaritalStatus,
        emp.Photo,
        emp.OfficialEmail, 
        j.JobTitle,
        i.PassportNumber, 
        convert(varchar(10),i.PassportStartDate,10) as PassportStartDate,
        convert(varchar(10),i.PassportEndDate,10) as PassportEndDate,
        convert(varchar(10),i.I94StartDate,10) as I94StartDate,
        convert(varchar(10),i.I94EndDate,10) as I94EndDate,
        convert(varchar(10),i.VisaStartDate,10) as VisaStartDate,
        convert(varchar(10),i.VisaEndDate,10) as VisaEndDate,
        convert(varchar(10),i.PetitionStartDate,10) as PetitionStartDate,
        convert(varchar(10),i.PetitionEndDate,10) as PetitionEndDate,
        convert(varchar(10),i.WACNumber,10) as WACNumber,
        convert(varchar(10),i.PetitionRenewalAppliedDate,10) as PetitionRenewalAppliedDate,
        convert(varchar(10),i.PetitionRFERespondedDate,10) as PetitionRFERespondedDate,
        convert(varchar(10),i.I140ApprovedDate ,10) as I140ApprovedDate,
        convert(varchar(10),i.I1485ApprovedDate,10) as I1485ApprovedDate,
        i.GCCategoryType, 
        it.Name as Immigrationtypename, 
        stuff((
            select '|'+ DependentName 
            from EmployeeDependents ed 
            where ed.UserId=emp.Userid 
            for xml path('')),1,1,'') as DependentName,
        p.ProjectName, 
        stuff((
            select ','+ Description 
            from TimeTrackerEntryTable as t 
            where t.Userid=emp.userid 
            for xml path('')),1,1,'') as PDescription,
        stuff((
            select ','+ ProvidedChequeNo 
            from LoanProvidedDetail as lp 
            where lp.UserId=emp.UserId 
            for xml path('')),1,1,'') as ProvidedChequeNo,
        stuff((
            select ','+ ProvidedAmount 
            from LoanProvidedDetail as lp 
            where lp.UserId=emp.UserId 
            order by lp.provideddate desc 
            for xml path('')),10,1,'') as ProvidedAmount,
        stuff((
            select ','+ CurrentChequeNo 
            from LoanPaymentDetails as lp 
            where lp.UserId=emp.UserId 
            order by lp.paymentdate desc 
            for xml path('')),1,1,'') as CurrentChequeNo,
        stuff((
            select ','+ir.ChequeNo 
            from ImmigrationRepeatableGroup as ir 
            where ir.ImmigrationId=irg.ImmigrationId 
            order by ir.PaymentDate desc 
            for xml path('')),1,1,'') as ImmigrationChequeNo
    from EmployeeBasicDetails as emp 
    left outer join JobtitleDDTable as j 
        on j.Id=emp.JobTitle 
    inner JOIN EmployeeImmegrationDetails as i
        on emp.UserId=i.UserId
    left outer join TimeTrackerEntryTable k 
        on i.UserId = k.UserId 
    left outer join ProjectDetails p 
        on p.ProjectId = k.ProjectId 
    left outer join LoanProvidedDetail lpd 
        on lpd.UserId=emp.UserId 
    left outer join LoanPaymentDetails lp 
        on lp.UserId=emp.UserId 
    inner join EduQualificationDDTable as eq 
        on emp.Qualification=eq.Id 
    inner join ImmigrationType as it 
        on it.ImmigrationTypeId=i.ImmigrationStatusType 
    left outer join ImmigrationRepeatableGroup as irg
        on irg.ImmigrationId=i.ImmigrationId
    group by 
        emp.UserId,
        emp.EmployeeId,
        emp.FirstName,
        emp.MiddleName,
        emp.LastName,
        emp.MobileNumber,
        emp.HomeNumber,
        emp.OfficeNumber, 
        emp.OffshoreCN1,
        emp.OffshoreCN2,
        emp.OffshoreCN3,
        emp.OffshoreCN4,
        emp.OffshoreCN5,
        emp.OffshoreCN6,
        emp.OffshoreCN7, 
        emp.OffshoreCN8,
        emp.OffshoreCN9,
        emp.OffshoreCN10,
        emp.PersonalEmail,
        emp.PersonalAddress,
        emp.Zipcode, 
        emp.BloodGroup,
        emp.DOB,
        emp.city,
        emp.State,
        emp.MaritalStatus,
        emp.Photo,
        emp.OfficialEmail, j.JobTitle,
        i.PassportNumber,
        p.ProjectName,
        eq.EducationalQualification,
        USJoindate,
        IndiaJoindate,
        BackgroundVerificationdate, 
        PassportStartDate,
        PassportEndDate,
        it.Name,
        I94StartDate,
        I94EndDate,
        VisaStartDate,
        VisaEndDate,
        PetitionStartDate, 
        PetitionEndDate,
        WACNumber,
        PetitionRenewalAppliedDate,
        PetitionRFERespondedDate,
        I140ApprovedDate,
        I1485ApprovedDate, 
        i.GCCategoryType,
        irg.ImmigrationId
), 
q2 as
(
    select 
        Userid, 
        DependentName1, 
        DependentName2, 
        DependentName3, 
        DependentName4, 
        DependentName5, 
        DependentName6, 
        DependentName7, 
        DependentName8, 
        DependentName9 
    FROM 
    ( 
        select 
            Userid, 
            col+cast(rn as varchar(10)) col, 
            value 
        from 
        (
            select 
                Userid, 
                DependentName, 
                row_number() over(
                    partition by Userid 
                    order by DependentName) rn 
            from EmployeeDependents 
        ) t 
        cross apply 
        ( 
            select 
                'DependentName', 
                DependentName
        ) c (col, value) 
    ) src 
    pivot 
    ( 
        max(value) 
        for col in 
        (
            DependentName1, 
            DependentName2, 
            DependentName3, 
            DependentName4, 
            DependentName5, 
            DependentName6, 
            DependentName7, 
            DependentName8, 
            DependentName9
        ) 
    ) piv
)
select *
from q1 
join q2 
    on q1.UserId = q2.UserId;

Leave a Reply

Your email address will not be published. Required fields are marked *