Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    66
    Provided Answers: 1

    Question Unanswered: Looping through a MS SQL comma seperated cell and then combine

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

    Code:
    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:
    Code:
    ------------------------------------------------
    ID    | employeeID | userPerms | pagePermPAges
    ------------------------------------------------
    15    | 3FdFieu9I  | 1,3       | 1,4,5,6
    The userPermissions table looks like this:
    Code:
    ----------------
    ID | type
    ----------------
    1  | Read
    2  | Write
    3  | Upload
    4  | Admin
    And this is my pagePermissions table looks like:
    Code:
    ----------------
    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:
    Code:
    -------------------------------------------------------------------------------------------------------
    ID    | employeeID | userPermissions                         | pagePermissions
    -------------------------------------------------------------------------------------------------------
    15    | 3FdFieu9I  | Read,Read,Read...upload,upload,upload...| Read,Read,Read...upload,upload,upload...
    It should read this though:
    Code:
    -------------------------------------------------------------------------
    ID    | employeeID | userPermissions | pagePermissions
    -------------------------------------------------------------------------
    15    | 3FdFieu9I  | Read,Upload     | bindex,form,information,myPage
    Any MS SQL guru out there that can help me out?

  2. #2
    Join Date
    Jul 2003
    Posts
    66
    Provided Answers: 1
    Here is the correct one:
    Code:
    SELECT 
    	fullTbl.ID,
    	fullTbl.employeeID,
        userPermissions = (
    		SELECT 
    			Stuff((SELECT 
    					  DISTINCT ',' + uP.type 
    				   FROM 
    					  usersPermissions AS uP
    				   WHERE 
    				      charindex(
    						concat(',', uP.ID, ','),
    						concat(',', fullTbl.userPerms, ',')
    					  ) > 0 
    				   FOR 
    					  XML Path ('')),1,1,''
    			)),
    	pagePermissions = (
    		SELECT 
    			Stuff((SELECT 
    					  DISTINCT ',' + pP.name 
    				   FROM 
    					  pagePermissions AS pP 
    				   WHERE 
    					  charindex(
    						concat(',', pP.ID, ','),
    						concat(',', fullTbl.userPermPages, ',')
    					  ) > 0 
    				   FOR 
    					  XML Path ('')),1,1,''
    			))
     FROM 
    	usersTbl AS fullTbl
     WHERE 
    	fullTbl.active='true'
     AND 
    	fullTbl.email='bbarker@thepriceisright.com'

  3. #3
    Join Date
    Dec 2016
    Posts
    8

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •