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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply