November 4, 2015 at 4:53 am
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.
November 4, 2015 at 7:50 am
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.
November 4, 2015 at 8:20 am
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
November 4, 2015 at 8:52 am
Awesome Drew. This works. Thank you both. J.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy