After seeing a couple of questions of using SQL to create comma-separated lists, I created this select to create a comma-separated list of multi-value attributes.
The query is created for MS SQL-server, but will also work for Oracle with minor changes (datatype and concatenate operator).
I have used the MXREF_MX_PRIVILEGE as an example. Note that there are a limit on the length of the concatenated string.
WITH Ranked (USERID, rnk, PRIVILEGES)
AS (SELECT e1.mcMSKEYVALUE as USERID,
ROW_NUMBER() OVER( PARTITION BY e1.mcMSKEYVALUE ORDER BY e1.mcMSKEYVALUE),
cast(e2.mcMSKEYVALUE AS NVARCHAR(4000)) as PRIVILEGES
FROM idmv_entry_simple e1
inner join idmv_vallink_ext v on e1.mcmskey = v.mcmskey
inner join idmv_entry_simple e2 on cast(e2.mcmskey as varchar) = v.mcsearchvalue
where v.mcAttrName = 'MXREF_MX_PRIVILEGE'),
AnchorRanked (USERID, rnk, PRIVILEGES)
AS ( SELECT USERID, rnk, PRIVILEGES
FROM Ranked
WHERE rnk = 1),
RecurRanked (USERID, rnk, PRIVILEGES)
AS (SELECT USERID, rnk, PRIVILEGES
FROM AnchorRanked
UNION ALL
SELECT Ranked.USERID, Ranked.rnk,
RecurRanked.PRIVILEGES + ', ' + Ranked.PRIVILEGES
FROM Ranked
INNER JOIN RecurRanked
ON Ranked.USERID = RecurRanked.USERID
AND Ranked.rnk = RecurRanked.rnk + 1 )
SELECT USERID, MAX(PRIVILEGES) FROM RecurRanked
GROUP BY USERID;
Enjoy.
Ole K.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 |