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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply