xml path approach for appending rows horizontally...

  • Hi,

    I'm trying to append rows horizontally - I'm using the "xml path" approach

    SELECT

    E.[USER_NAME] As 'User Name',

    (

    SELECT ',' + C.[PERMISSION_NAME]

    FOR XML PATH('')

    ) As [Associated Groups]

    FROM TABLEA As A

    JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF

    JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF

    JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID

    JOIN TABLEE AS E ON E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF

    WHERE A.[OBJECT_NAME] = 'MyObjectName'

    It's not working. I'm getting:

    User nameAssociated Groups

    A. SmithG1

    A. SmithG2

    A. SmithG3

    etc...

    What I'm looking for is:

    User NameAssociated Groups

    A. SmithG1, G2, G3

    etc...

    Would greatly appreciate any assistance you can offer me on this one.

    Thanks,

    J.

  • You need to bring your JOINs to the sub-query FOR XML PATH.

    If you provide DDL for your objects and some test data setup I could give you exact and details answer.

    Hints are in the article - link is in my signature.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Try the following:

    SELECT

    E.[USER_NAME] As 'User Name',

    (

    SELECT ',' + C.[PERMISSION_NAME]

    FROM TABLEA As A

    JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF

    JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF

    JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID

    WHERE E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF

    AND A.[OBJECT_NAME] = 'MyObjectName'

    FOR XML PATH('')

    ) As [Associated Groups]

    FROM TABLEE AS E

    If that doesn't work, then post the info that Eugene asked for.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Awesome Drew. This works. Thank you both. J.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply