2017年4月18日 星期二

I am trying to join three tables and concat 2 different column into multiple rows

I am trying to join three tables and concatenate 2 different columns into multiple rows. I have four columns of data when I run a regular query with multiple filenames and multiple phone numbers. I want to get rid of the duplicates that result with the query.
I would like to have the id not duplicated with the different filenames concatenated into another column for id separated by commas. The same thing should be true with all the phone numbers concatenated into one row with the corresponding id and separated by comma.
I found some examples using xml path, but they were with only two tables and two columns.
Below I have the three tables, the query I have so far, and the query I wish to have. Does anyone know how to do nested xml paths while joining tables?
Table:  IPMFILE
ID                   FILENAME
10000.000     10000_45109.doc
10001.000     10001_45115.docx
10002.000     10002_47752.doc
10002.000     10002_45119.doc
10003.000     10003_45123.doc
10004.000     10004_45128.docx
10004.000     10004_45183.docx
Table:   Persons
ID                   CITY
10000.000     Elkins Park
10001.000     4504 St. James Drive Plano
10002.000     Fort Collins
10003.000     Bound Brook
10004.000     Downers Grove
Table:   Phone
ID                   phone
10000.000     215-7177179
10001.000     972-6187143
10002.000     970-4439376
10003.000     Home 732-3693106
10004.000     C 630-4648539
10004.000     H 630-9681673
SELECT PERSONS.ID, impfile.FILENAME, phonenumbers.phone, PERSONS.CITY 
FROM IMPFILE, Persons, Phonenumbers WHERE impfile.ID=Persons.ID 
AND phonenumbers.id=persons.id AND PERSONS.ID 
BETWEEN 10000 AND 10004 order by person


Result of Query
ID                  FILENAME                phone                         CITY
10000.000   10000_45109.doc        215-7177179              Elkins Park
10001.000    10001_45115.docx      972-6187143              4504 St. James Drive Plano
10002.000    10002_47752.doc,       970-4439376              Fort Collins
10002.000    10002_45119.doc        970-4439376              Fort Collins
10003.000    10003_45123.doc        Home 732-3693106             Bound Brook
10004.000    10004_45128.docx      C 630-4648539              Downers Grove
10004.000    10004_45183.docx      C 630-4648539              Downers Grove
10004.000    10004_45128.docx      H 630-9681673              Downers Grove
10004.000    10004_45183.docx      H 630-9681673               Downers Grove
Desired Query with Filename Concatenated for each id and Phone number concatenated for each id and duplicates removed
ID                    FILENAME                                             phone                        CITY
10000.000         10000_45109.doc                             215-7177179                          Elkins Park
10001.000        10001_45115.docx                            972-6187143                4504 St. James Drive Plano
10002.000        10002_47752.doc, 10002_45119.doc        970-4439376                Fort Collins
10003.000        10003_45123.doc                            Home 732-3693106               Bound Brook
10004.000        10004_45128.docx, 10004_45183.docxC     630-4648539,H 630-9681673       Downers Grove
I found this on another post:
SELECT * 
FROM   ThisTable 
   OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()] 
                        FROM   SomeTable 
                        WHERE  SomeTable.ID = ThisTable.ID 
                        FOR XML PATH ('')) AS ConcatenatedSomeField) A 
   OUTER APPLY (SELECT (SELECT SomeField2 + ' ' AS [data()] 
                        FROM   SomeTable 
                        WHERE  SomeTable.ID = ThisTable.ID 
                        FOR XML PATH ('')) AS ConcatenatedSomeField2) B 
   OUTER APPLY (SELECT (SELECT SomeField3 + ' ' AS [data()] 
                        FROM   SomeTable 
                        WHERE  SomeTable.ID = ThisTable.ID 
                        FOR XML PATH ('')) AS ConcatenatedSomeField3) C 

from : http://stackoverflow.com/questions/23863792/i-am-trying-to-join-three-tables-and-concat-2-different-column-into-multiple-row

沒有留言:

張貼留言