combine data of multiple rows from multiple tables and show in single row

Question

I am writing query for getting result from multiple table it gives multiple rows with repeating values in all column expect one i want that columns to be short and show in single row, these are my tables

Table Name: AppUesrs

Id  Name    Gender
1   Goga    M
2   Maja    M
3   Phadu   M
4   Kaku    F
5   Seefa   F

Table Name: AppProducts

Id  Name    Value
1   Bc090   10
2   Bc080   15
3   Mc070   2
4   Mc100   16
5   Bc110   15

Table Name: AppOrders

Id  Date        ExpDate
1   08/9/2014   10/10/2015
2   18/9/2014   08/11/2015
3   20/9/2014   25/12/2015
4   01/10/2014  14/12/2015
5   19/10/2014  15/2/2016

and Table Name: ProductOwners

OId  PId UId
1   1   2
1   2   2
1   5   2
2   3   5
3   4   4
3   3   4
3   5   4
3   1   4

I am writing query for it and it show data like this

select O.Id, P.ProductName, U.Name ,O.Date,O.ExpDate 
from AppProductOwners PO, AppProducts P, AppOrders O, AppUsers U 
Where PO.AppOrderId = O.Id AND PO.ProductsId = P.Id AND PO.AppUserId = U.Id

it is giving data like

O.Id    P.Name  U.Name  O.Date  O.ExpDate
   1    Bc090   Maja    08/9/2014   10/10/2015
   1    Bc080   Maja    08/9/2014   10/10/2015
   1    Bc110   maja    08/9/2014   10/10/2015

and i want data like

  O.Id  P.Name             U.Name   O.Date  O.ExpDate
   1    Bc090,Bc080,Bc110   Maja    08/9/2014   10/10/2015

Kindly if any one help me writing this query

If I write it like

SELECT  O.ID,
    PName = STUFF(( SELECT  ',' + P.ProductName
                    FROM   AppProductOwners PO
                            INNER JOIN AppProducts P
                                ON P.Id = PO.ProductsId
                    AND   U.Id = PO.AppUserId
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
    U.FirstName, U.LastName,
    O.Date,
    o.ExpDate
FROM    AppUsers U
    INNER JOIN AppOrders O
        ON u.id = O.ID;

it shows nothing , and if i write it like

SELECT  O.ID,
    PName = STUFF(( SELECT  ',' + P.ProductName
                    FROM   AppProductOwners PO
                            INNER JOIN AppProducts P
                                ON P.Id = PO.ProductsId
                    AND   AppUsers.Id = PO.AppUserId
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
    U.FirstName, U.LastName,
    O.Date,
    o.ExpDate
FROM    AppUsers U
    INNER JOIN AppOrders O
        ON u.id = O.ID;

It gives error

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "AppUsers.Id" could not be bound.

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

You can use SQL Servers XML extensions to concatenate rows into a single column:

SELECT  O.ID,
        PName = STUFF(( SELECT  ',' + P.Name
                        FROM    PO
                                INNER JOIN P
                                    ON P.ID = PO.PID
                        WHERE   U.ID = PO.UID
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        U.Name,
        O.Date,
        o.ExpDate
FROM    U
        INNER JOIN O
            ON u.id = O.ID;

For a more detailed explanation of how this works see this answer

If you only want to include rows where PName is not null, then it is probably easier to adjust the query slightly to use CROSS APPLY:

SELECT  O.ID,
        PName = STUFF(p.PName.value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        U.Name,
        O.Date,
        o.ExpDate
FROM    U
        INNER JOIN O
            ON u.id = O.ID
        CROSS APPLY
        (   SELECT  ',' + P.Name
            FROM    PO
                    INNER JOIN P
                        ON P.ID = PO.PID
            WHERE   U.ID = PO.UID
            FOR XML PATH(''), TYPE
        ) p (PName)
WHERE   p.Pname IS NOT NULL;

Examples on SQL Fiddle

EDIT

I think I misunderstood your schema initially, and how Orders linked to Users, I think this will work as required:

SELECT  O.ID,
        PName = STUFF(p.PName.value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        U.Name,
        O.Date,
        o.ExpDate
FROM    AppUsers AS U
        CROSS JOIN AppOrders AS O
        CROSS APPLY
        (   SELECT  ',' + P.Name
            FROM    AppProductOwners AS PO
                    INNER JOIN AppProducts AS P
                        ON P.ID = PO.PID
            WHERE   U.ID = PO.UID
            AND     O.ID = PO.OID
            FOR XML PATH(''), TYPE
        ) p (PName)
WHERE   p.Pname IS NOT NULL;

edited May 23 '17 at 12:05

Community

1
1

answered Oct 27 '14 at 08:15

GarethD

68,045
10
83
123

it is not working . Kindly will u recheck the answer ? – Asad kamal Oct 27 '14 at 10:18
Can you elaborate on *"it is not working"* please - Any error message? Not the results you are expecting? My knowledge of SQL is not bad, I am however rubbish at mind reading. – GarethD Oct 27 '14 at 10:28
Dear first it gives error "The multi-part identifier "U.ID" could not be bound." if we gives reference of U from below part it shows empty .. – Asad kamal Oct 27 '14 at 10:33
Then one of two things is happening - either your table structure is not what you have posted in the question (is your table actually called "U"?), or you are not running the exact queries I have posted. The SQL Fiddle provided in the answer shows that both queries work fine. – GarethD Oct 27 '14 at 10:38
My table name is Users , i write it Users as U, it made easy to fetch fields from table, and i post the same query , u can say i copy past your code and name it as i write in question – Asad kamal Oct 27 '14 at 10:59
my table structure is same as i provided except their names – Asad kamal Oct 27 '14 at 11:01
I don't know what more I can do to help, as I have said the queries work fine for me (on SQL Server 2008, and 2012), the error `The multi-part identifier "U.ID" could not be bound.` indicates either that you don't have a table called `U`, or none of the tables have an alias `U`, or the table does not contain a column `ID`. What version of SQL Server are you using? – GarethD Oct 27 '14 at 11:12
I checked it again with every single detail , i am using SQL SERVER 2008 R2 – Asad kamal Oct 27 '14 at 11:27
Can you please edit the question and show the **Exact** query you are running, and the line at which the error is occurring. Thanks – GarethD Oct 27 '14 at 11:41
I Update my Question now it is same as i am doing – Asad kamal Oct 27 '14 at 12:04

combine data of multiple rows from multiple tables and show in single row

1 Answers1