October 8, 2015 at 4:46 am
Hi,
Can you help me please? I'm using the SQL indicated below:
SELECT
DISTINCT C.Field1 As 'Group',
A.Field2 As 'Security Object'
E.Field3 As 'User'
FROM TableA AS A
JOIN TableB AS B ON B.key1 = A.Key1
JOIN TableC AS C ON C.Key1 = B.key2
JOIN TableD AS D ON D.key1 = C.key2
where A.Field1 = 'ObjectA'
GROUP BY C.Field1, A.Field2, E.Field3
Typical Output
GroupSecurity Object User
Group1ObjectAUserA
Group1ObjectAUserB
Group1ObjectAUserC
Group2ObjectAUserE
Group2ObjectAUserF
Group2ObjectAUserH
etc...
I want to output something more like:
GroupSecurity Object User
Group1ObjectAUserA, UserB, UserC
Group2ObjectAUserE, UserF, UserH
etc
However I'm having difficulties concatenating the user field in the horizontal form indicated. Can you help me please?
I know there is lots about this online, but nothing is working properly 🙁
J.
October 8, 2015 at 4:55 am
jellybean (10/8/2015)
Hi,Can you help me please? I'm using the SQL indicated below:
SELECT
DISTINCT C.Field1 As 'Group',
A.Field2 As 'Security Object'
E.Field3 As 'User'
FROM TableA AS A
JOIN TableB AS B ON B.key1 = A.Key1
JOIN TableC AS C ON C.Key1 = B.key2
JOIN TableD AS D ON D.key1 = C.key2
where A.Field1 = 'ObjectA'
GROUP BY C.Field1, A.Field2, E.Field3
Typical Output
GroupSecurity Object User
Group1ObjectAUserA
Group1ObjectAUserB
Group1ObjectAUserC
Group2ObjectAUserE
Group2ObjectAUserF
Group2ObjectAUserH
etc...
I want to output something more like:
GroupSecurity Object User
Group1ObjectAUserA, UserB, UserC
Group2ObjectAUserE, UserF, UserH
etc
However I'm having difficulties concatenating the user field in the horizontal form indicated. Can you help me please?
I know there is lots about this online, but nothing is working properly 🙁
J.
You've been here long enough to know the drill. Please post sample DDL and sample data, in consumable form, as per the link in my signature.
October 9, 2015 at 9:48 am
concatenate using FOR XML PATH.
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
October 14, 2015 at 4:34 pm
Hello,
Since I don't have your DDL and sample data. I generated some data and tried this.
Try something like this. (Check attached file)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply