Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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.

Labels in this area