How to get column values in a comma separated row values ?

  • 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

    JohnsonFinnegan |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

    JohnsonFinnegan |Henderson| 16150.01 |2010-04-27 |10004 | Adverse

    JohnsonFinnegan |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.

  • 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

  • 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

  • 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 ?

  • 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

  • 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