February 9, 2012 at 3:38 pm
Please help, as I have been trying for a while..here is the scenario;
1. I have to query multiple tables that are connected via joins
2. My result set is distinct, EXCEPT when I pull the last field, which duplicates my result set except the last field
3. I want to be able to put the column values into a row field, separated by commas, so I will have a distinct result set
Here is the example;
SELECT DISTINCT a.PROJECT_NM, a.CLIENT_NM, a.PROJECT_CD, a.Date, a.OFFICE
FROM INT_AUX_PROJECT a INNER JOIN INT_AUX_DIR_LIST l ON a.DIRECTORY_ID = l.CONTAIN_DIR_ID INNER JOIN
INT_AUX_LISTING al ON l.LISTING_ID = al.LISTING_ID INNER JOIN
INT_AUX_PROJMEMBER_ROLE pr ON a.PROJECT_ID = pr.PROJECT_ID AND
al.LISTING_ID = pr.LISTING_ID INNER JOIN
INT_PROJ_ROLE_TYP it ON pr.PROJ_ROLE_TYP_ID = it.PROJ_ROLE_TYP_ID
WHERE (al.DISPLAY_NM = N'XYZ.') AND (a.DELETE_IND = 0)
Result Set
-----
Project_NM | CLIENT_NM| PROJECT_CD| Date | Office
Johnson Finnegan |Henderson| 16150.01 |2010-04-27 |10004
Now...when I add the last field...the result set looks like this;
SELECT DISTINCT a.PROJECT_NM, a.CLIENT_NM, a.PROJECT_CD, a.Date, a.OFFICE,it.ROLE
FROM INT_AUX_PROJECT a INNER JOIN INT_AUX_DIR_LIST l ON a.DIRECTORY_ID = l.CONTAIN_DIR_ID INNER JOIN
INT_AUX_LISTING al ON l.LISTING_ID = al.LISTING_ID INNER JOIN
INT_AUX_PROJMEMBER_ROLE pr ON a.PROJECT_ID = pr.PROJECT_ID AND
al.LISTING_ID = pr.LISTING_ID INNER JOIN
INT_PROJ_ROLE_TYP it ON pr.PROJ_ROLE_TYP_ID = it.PROJ_ROLE_TYP_ID
WHERE (al.DISPLAY_NM = N'XYZ') AND (a.DELETE_IND = 0)
Result Set;
---------
Project_NM | CLIENT_NM | PROJECT_CD | Date | Office| Role
Johnson Finnegan |Henderson| 16150.01 |2010-04-27 |10004 | Adverse
Johnson Finnegan |Henderson| 16150.01 |2010-04-27 |10004 | Client
What I want is just one row, with Role looking like "Adverse, Client".......HOW do I do that ?...please keep it as simple as possible
This query works if I only select the Role field, but how do I incorporate it into my query where I have several fields and tables ?
SELECT SUBSTRING(
(SELECT ',' + ROLE
FROM INT_PROJ_ROLE_TYP
FOR XML PATH('')),2,200000) AS Role
Please help, thank you.
February 9, 2012 at 5:24 pm
SELECT DISTINCT a.PROJECT_NM, a.CLIENT_NM, a.PROJECT_CD, a.Date, a.OFFICE,
ROLE = STUFF((
SELECT ',' + ROLE
FROM INT_PROJ_ROLE_TYP iprt
WHERE pr.PROJ_ROLE_TYP_ID = iprt.PROJ_ROLE_TYP_ID
FOR XML PATH('')
), 1, 1, '')
FROM INT_AUX_PROJECT a
INNER JOIN INT_AUX_DIR_LIST l
ON a.DIRECTORY_ID = l.CONTAIN_DIR_ID
INNER JOIN INT_AUX_LISTING al
ON l.LISTING_ID = al.LISTING_ID
INNER JOIN INT_AUX_PROJMEMBER_ROLE pr
ON a.PROJECT_ID = pr.PROJECT_ID
AND al.LISTING_ID = pr.LISTING_ID
INNER JOIN INT_PROJ_ROLE_TYP it
ON pr.PROJ_ROLE_TYP_ID = it.PROJ_ROLE_TYP_ID
WHERE (al.DISPLAY_NM = N'XYZ')
AND (a.DELETE_IND = 0)
Hope this does the trick.
-- Gianluca Sartori
February 9, 2012 at 5:26 pm
Looking closer at your code, you could also eliminate the JOIN with INT_PROJ_ROLE_TYP:
SELECT DISTINCT a.PROJECT_NM, a.CLIENT_NM, a.PROJECT_CD, a.Date, a.OFFICE,
ROLE = STUFF((
SELECT ',' + ROLE
FROM INT_PROJ_ROLE_TYP iprt
WHERE pr.PROJ_ROLE_TYP_ID = iprt.PROJ_ROLE_TYP_ID
FOR XML PATH('')
), 1, 1, '')
FROM INT_AUX_PROJECT a
INNER JOIN INT_AUX_DIR_LIST l
ON a.DIRECTORY_ID = l.CONTAIN_DIR_ID
INNER JOIN INT_AUX_LISTING al
ON l.LISTING_ID = al.LISTING_ID
INNER JOIN INT_AUX_PROJMEMBER_ROLE pr
ON a.PROJECT_ID = pr.PROJECT_ID
AND al.LISTING_ID = pr.LISTING_ID
WHERE (al.DISPLAY_NM = N'XYZ')
AND (a.DELETE_IND = 0)
-- Gianluca Sartori
February 10, 2012 at 12:23 pm
Thank you for the try, but unfortunately, I still get two rows due to the two different roles and your script does not put them in the same cell. 🙁
Would a grouping syntax help ?
February 11, 2012 at 2:49 pm
Maybe.
If you post table scripts, sample data and expected results based on sample data things would be much easier for us.
Please, read the article linked n my signature line and find out ho to post to get the best help on the forums.
-- Gianluca Sartori
April 6, 2012 at 6:47 pm
So working with Interaction eh?...
You could look at doing a sub-select I guess or perhaps even a pivot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply