Convert Rows to CSV & Eliminate Duplicates in SQL Server

Problem Statement

We have a user who is part of multiple roles, and each role is linked to multiple privileges. Users want to see the roles and privileges associated with each user. The outcome should contain unique roles and privileges, ensuring there are no repetitive roles or privileges. Essentially, we need to eliminate duplicates in the comma-separated string.

Let's examine the actual and expected data.

Solution

To address this issue using the STRING_AGG() function in SQL Server, you can choose between the following two approaches.

Approach 1

By using STRING_AGG() within a subquery, we can effectively remove any duplicates.

SELECT RL.UserName, STRING_AGG(RL.RoleName,', ') AS RoleName, PL.PrivilegeName
FROM (
	SELECT DISTINCT U.UserId, U.Name AS UserName, R.RoleName
	FROM #User U
	INNER JOIN #UserRolePrivilegeMap URPM
	ON U.UserId = URPM.UserId
	INNER JOIN #Role R
	ON URPM.RoleId = R.RoleId) RL
	INNER JOIN (
		SELECT P.UserId, STRING_AGG(P.PrivName,', ') AS PrivilegeName
		FROM (SELECT DISTINCT U.UserId, P.PrivName
			  FROM #User U
			  INNER JOIN #UserRolePrivilegeMap URPM
				ON U.UserId = URPM.UserId
			  INNER JOIN #Privilege P
				ON URPM.PrvId = P.PrvId) P
			  GROUP BY P.UserId) PL
		ON RL.UserId = PL.UserId
GROUP BY RL.UserName,PL.PrivilegeName

Expected Result

Approach 2

By using the grouped concatenation method with STRING_AGG(), we can convert rows into Comma-Separated Values (CSV). Subsequently, we can convert the CSV into XML and utilize the XQuery function distinct-values() to extract unique values from the XML instance.

/*Using XQuery-function distinct-values() get only distinct values*/
SELECT UserName
	   ,STUFF((RoleName.query('for $x in distinct-values(/x/text())return <x>{concat(",", $x)}</x>').value('.','varchar(250)')),1,1,'') AS RoleName
	   ,STUFF((PrivilegeName.query('for $x in distinct-values(/x/text())return <x>{concat(",", $x)}</x>').value('.','varchar(250)')),1,1,'') AS PrivilegeName
FROM(
SELECT U.Name As UserName
	  ,CAST('<x>' + REPLACE(STRING_AGG(R.RoleName,','),',','</x><x>') + '</x>' AS XML) AS   RoleName
	  ,CAST('<x>' + REPLACE(STRING_AGG(P.PrivName,','),',','</x><x>') + '</x>' AS XML) AS   PrivilegeName
FROM #User U
INNER JOIN #UserRolePrivilegeMap URPM
ON U.UserId = URPM.UserId
INNER JOIN #Role R
ON URPM.RoleId = R.RoleId
INNER JOIN #Privilege P
ON URPM.PrvId = P.PrvId
GROUP BY U.Name)A

Step 1. Utilizing the STRING_AGG() function, convert the actual data into the CSV format shown below.

Step 2. Next, convert the CSV into XML format.

Step 3. Now, utilize the XQuery function distinct-values() to extract unique values from the XML instance.

Tables and Insert Scripts

/*Create USER Table and Insert Data*/
DROP TABLE IF EXISTS #User
CREATE TABLE #User (UserId INT, Name VARCHAR(50))
INSERT INTO #User (UserId, Name) 
VALUES (1, 'John'),
	   (2, 'Peter'),
	   (3, 'David')

/*Create ROLE Table and Insert Data*/
DROP TABLE IF EXISTS #Role
CREATE TABLE #Role (RoleId INT, RoleName VARCHAR(50))
INSERT INTO #Role (RoleId, RoleName) 
VALUES (1, 'IT Admin'), (2, 'Developer'), (3, 'Sr.Developer'), (4, 'Lead'), (5, 'Sr.Lead')

/*Create PRIVILEGE Table and Insert Data*/
DROP TABLE IF EXISTS #Privilege
CREATE TABLE #Privilege (PrvId INT, PrivName VARCHAR(50))
INSERT INTO #Privilege (PrvId, PrivName)
VALUES (1, 'Default'), (2, 'Admin'), (3, 'Creator'), (4, 'Read'), (5, 'Write'), (6, 'Owner')

/*Create USERROLEPRIVILEGEMAP Table and Insert Data*/
DROP TABLE IF EXISTS #UserRolePrivilegeMap 
CREATE TABLE #UserRolePrivilegeMap(UserId INT, RoleId INT, PrvId INT)
INSERT INTO #UserRolePrivilegeMap (UserId, RoleId, PrvId)
VALUES (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,4), (1,2,5), (1,4,2),
	   (1,4,4), (2,1,1), (2,1,5), (2,1,3), (2,5,1), (2,5,2), (2,5,6),
	   (2,5,5), (2,5,3), (3,1,1), (3,1,6), (3,1,5), (3,1,4), (3,3,1),
	   (3,3,2), (3,3,4), (3,3,5), (3,3,6)

/*Join all tables and get the actual data*/
SELECT U.Name AS UserName
	  ,R.RoleName
	  ,P.PrivName AS PrivilegeName
FROM #User U
INNER JOIN #UserRolePrivilegeMap URPM
ON U.UserId = URPM.UserId
INNER JOIN #Role R
ON URPM.RoleId = R.RoleId
INNER JOIN #Privilege P
ON URPM.PrvId = P.PrvId

Conclusion

Efficiently convert row data into comma-separated values using SQL Server's string aggregate function, ensuring duplicates are removed for streamlined data representation and integrity.