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
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
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
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
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
沒有留言:
張貼留言