none
Multiple Tables Selection with Merging Columns & Rows in one. RRS feed

  • Question

  • I want to select different attributes from different tables. During this selection I want to select a combined attribute as merged rows and columns from one of the table grouped by ID.

    Here are the attributes and the tables which I need.

    What I got so far

    Here is the code in above Image.

    SELECT     GRNPayment.Payment_ID, GRNPayment.Invoice_ID, Suppliers.SName, Suppliers.CName,
    			Suppliers.Address, GRNPayment.TotalBalance,
    (Select STUFF((
            select ', ' + CONVERT(NVARCHAR(MAX), GRNItems.Item)
            + ' - ' + CONVERT(NVARCHAR(MAX), GRNItems.Meters)
            FROM GRN INNER JOIN
            GRNItems ON GRN.Invoice_ID  = GRNItems.Invoice_ID Inner JOIN
            Suppliers ON GRN.SID = Suppliers.SID 
    WHERE     (Suppliers.SName = N'Ali') AND (Suppliers.CName = N'ASons')
    		for xml path('')
    ), 1, 2, '')) AS [Items - Quantity]
    
    FROM         GRN INNER JOIN
                          GRNPayment ON GRN.Invoice_ID = GRNPayment.Invoice_ID INNER JOIN
                          Suppliers ON GRN.SID = Suppliers.SID
    WHERE     (Suppliers.SName = N'Ali') AND (Suppliers.CName = N'ASons')

     In the above Image columns and rows of Items and quantity  are combined without grouping by Invoice_ID, while I want it to be combined with grouped by Invoice_ID. Below is an edited Image sample(Just Sample to convey Question).

    Thanks in advance.


    Habib Ur Rehman


    • Edited by Habib ur rehman Thursday, March 26, 2020 6:18 PM An Image somehow got deleted.
    Thursday, March 26, 2020 6:16 PM

Answers

  • In the XML PATH sub query remove the JOIN to Suppliers. Change it instead to

    where exists (select 1 from Suppliers S where S.SName ... 

    AND S.SID = GRN.SID)

    Actually, also remove the JOIN to GRN in your subquery. It should be either OUTER APPLY or WHERE EXISTS to outer table. E.g. your select should only be from a single GRNItems in the subquery.

    Select STUFF((
            select ', ' + CONVERT(NVARCHAR(MAX), GRNItems.Item)
            + ' - ' + CONVERT(NVARCHAR(MAX), GRNItems.Meters)
            FROM 
            GRNItems where GRNItems.InvoiceNo = GRN.InvoiceNo -- reference outer table
    ORDER BY -- you may need some order here
    		for xml path('')
    ), 1, 2, '')) AS [Items - Quantity]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    Thursday, March 26, 2020 6:37 PM
    Moderator

All replies

  • In the XML PATH sub query remove the JOIN to Suppliers. Change it instead to

    where exists (select 1 from Suppliers S where S.SName ... 

    AND S.SID = GRN.SID)

    Actually, also remove the JOIN to GRN in your subquery. It should be either OUTER APPLY or WHERE EXISTS to outer table. E.g. your select should only be from a single GRNItems in the subquery.

    Select STUFF((
            select ', ' + CONVERT(NVARCHAR(MAX), GRNItems.Item)
            + ' - ' + CONVERT(NVARCHAR(MAX), GRNItems.Meters)
            FROM 
            GRNItems where GRNItems.InvoiceNo = GRN.InvoiceNo -- reference outer table
    ORDER BY -- you may need some order here
    		for xml path('')
    ), 1, 2, '')) AS [Items - Quantity]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    Thursday, March 26, 2020 6:37 PM
    Moderator
  • In the XML PATH sub........

    Select STUFF(( ........

    Thank you Naomi N for your valuable time.

    Please explain a bit How the Sub Query refers to the outer table? 


    Habib Ur Rehman


    Friday, March 27, 2020 7:57 AM
  • Hi Habib ur rehman, 

    Thank you for your posting,

     

    Could you  please share us your table structure and some sample data along with your expected result using script instead of picture ? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 27, 2020 7:59 AM
  • Hi Habib ur rehman, 

    .................

     

    Best Regards,

    Rachel

    Thank You Rachel_Wang,  Naomi N gave the exact solution.

    Habib Ur Rehman

    Friday, March 27, 2020 8:04 AM
  • Hi,

    For your particular case this is the reference

    GRNItems where GRNItems.InvoiceNo = GRN.InvoiceNo -- reference outer table

    GRN is the alias for the table used in your query, but it is not used inside the sub-query, so this would be an outer reference.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, March 27, 2020 1:01 PM
    Moderator
  • Hi,

    ....

    My TechNet articles

    Thanks.

    Habib Ur Rehman

    Friday, March 27, 2020 5:39 PM