Two comma seperated cell merge with final output

Question

Hey all I have the following SQL Query that I am using in order to get comma separated values from a cell:

WITH cte AS(
    SELECT        
        uT.id AS UID, 
        uT.employeeID, 
        uP.type, 
        pP.name 
    FROM 
        usersTbl AS uT
    CROSS APPLY 
        dbo.DelimitedSplit8K(uT.userPerms,',') AS uPcommaItems
    INNER JOIN 
        usersPermissions uP ON uP.id = uPcommaItems.Item
    CROSS APPLY 
        dbo.DelimitedSplit8K(uT.userPermPages,',') AS pPcommaItems
    INNER JOIN 
        pagePermissions pP ON pP.id = pPcommaItems.Item
)
SELECT DISTINCT 
    UID, 
    employeeID,
    STUFF(
            (
                SELECT 
                    ',' + stuff1.type
                FROM 
                    cte AS stuff1
                WHERE 
                    t.UID = stuff1.UID 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
          ) AS userPermissions,
    STUFF(
            (
                SELECT 
                    ',' + stuff2.type
                FROM 
                    cte AS stuff2
                WHERE 
                    t.UID = stuff2.UID
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
          ) AS pagePermissions
FROM 
    cte AS t

The userTbl table looks like this:

------------------------------------------------
ID    | employeeID | userPerms | pagePermPAges
------------------------------------------------
15    | 3FdFieu9I  | 1,3       | 1,4,5,6

The userPermissions table looks like this:

----------------
ID | type
----------------
1  | Read
2  | Write
3  | Upload
4  | Admin

And this is my pagePermissions table looks like:

ID | name
----------------
1  | bindex
2  | flight
3  | submit
4  | form
5  | information
6  | myPage
7  | register

My current output of the query above is this:

-------------------------------------------------------------------------------------------------------
ID    | employeeID | userPermissions                         | pagePermissions
-------------------------------------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Read,Read...upload,upload,upload...| Read,Read,Read...upload,upload,upload...

It should read this though:

-------------------------------------------------------------------------
ID    | employeeID | userPermissions | pagePermissions
-------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Upload     | bindex,form,information,myPage

Any MS SQL guru out there that can help me out?

Don't store comma separated strings in tables. Use a proper junction table. It is one thing to create a query that has the right output. That is just for output purposes. That is the wrong way to store the data. — Gordon Linoff, Aug 15 '17 at 20:21
As Gordon said...any many others on your last post for this same topic the problem is your data structure is wrong. If you are deadset on sticking with this denormalized nightmare you are going to have to use STUFF and FOR XML after splitting your strings to squash them back into the delimited list you want. — Sean Lange, Aug 15 '17 at 20:25
Looks an *awful lot* like your previous question: https://stackoverflow.com/questions/45696509/sql-server-stored-procedure-looping-through-a-comma-delimited-cell ..... — marc_s, Aug 15 '17 at 20:41

score 2 · Accepted Answer · edited Aug 15 '17 at 21:07

2

Perhaps this may help

Example

Select A.ID
      ,A.employeeID
      ,usersPermissions = Stuff((Select Distinct ',' +[type] From userPermissions Where charindex(concat(',',ID,','),concat(',',A.[userPerms]    ,','))>0 For XML Path ('')),1,1,'') 
      ,pagePermissions = Stuff((Select Distinct ',' +[name] From pagePermissions Where charindex(concat(',',ID,','),concat(',',A.[userPermPages],','))>0 For XML Path ('')),1,1,'') 
 From usersTbl A

Returns

edited Aug 15 '17 at 21:07

StealthRT

10,108
40
183
342

answered Aug 15 '17 at 20:49

John Cappelletti

79,615
7
44
66

Where did **pagePermPAges** come from John? – StealthRT Aug 15 '17 at 21:01
@StealthRT pagePermPAges was listed in your userTbl – John Cappelletti Aug 15 '17 at 21:03
@StealthRT Thought is looked odd, but I took it at face value. – John Cappelletti Aug 15 '17 at 21:05
Thanks for the help, John! :) – StealthRT Aug 15 '17 at 21:07
@StealthRT Happy to help – John Cappelletti Aug 15 '17 at 21:08

score 1 · Answer 2 · answered Aug 15 '17 at 20:30

You need to get just the unique values for the permissions. You can get there via DISTINCT or GROUP BY. Here's your code modified using the former method:

WITH cte AS(
    SELECT        
        uT.id AS UID, 
        uT.employeeID, 
        uP.type, 
        pP.name 
    FROM 
        usersTbl AS uT
    CROSS APPLY 
        dbo.DelimitedSplit8K(uT.userPerms,',') AS uPcommaItems
    INNER JOIN 
        usersPermissions uP ON uP.id = uPcommaItems.Item
    CROSS APPLY 
        dbo.DelimitedSplit8K(uT.userPermPages,',') AS pPcommaItems
    INNER JOIN 
        pagePermissions pP ON pP.id = pPcommaItems.Item
)
SELECT DISTINCT 
    UID, 
    employeeID,
    STUFF(
            (
                SELECT DISTINCT
                    ',' + stuff1.type
                FROM 
                    cte AS stuff1
                WHERE 
                    t.UID = stuff1.UID 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
          ) AS userPermissions,
    STUFF(
            (
                SELECT DISTINCT
                    ',' + stuff2.type
                FROM 
                    cte AS stuff2
                WHERE 
                    t.UID = stuff2.UID
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
          ) AS pagePermissions
FROM 
    cte AS t

Doing the **DISTINCT** helps but it just has the same values in both cells **Upload,Read** — StealthRT, Aug 15 '17 at 20:44
You could not have expected otherwise, as both of your nested queries are 100% identical. — Eli, Aug 15 '17 at 20:49

score 0 · Answer 3 · answered Aug 15 '17 at 20:27

0

I think this should do it if you Add

Group by

Before For XML

answered Aug 15 '17 at 20:27

BobNoobGuy

1,551
2
30
62

Two comma seperated cell merge with final output

And this is my pagePermissions table looks like:

3 Answers3