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