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

    Question Unanswered: MS SQL 2016 T-SQL convert comma string to inner join database name(s)

    Hey all I have a value for one of my columns that looks like this:
    Code:
        ID   | userPerms | Name      | DOB
        ----------------------------------------
        5985 |1,3,4      |Bob Barker |12/12/1923
        895  |1,2        |Bill Gates |10/14/1955
        5897 |1,2,4      |Steve Jobs |02/24/1955
    That column being the userPerms column.

    I need to be able to Inner Join with the userPerm table associated with those numbers.

    My query is currently:
    Code:
        SELECT 
        	uT.employeeID + '|' + uT.lastFirstMiddle + '|' + uT.ntName + '|' + uT.email + '|' + 
        	uT.firstName  + '|' + uT.lastName        + '|' + uT.active + '|' + uT.userPerms + '|' + 
            uT.userPermPages 
        FROM 
        	usersTbl AS uT
        INNER JOIN 
        	usersPermissions AS uP
        	ON uP.id = uT.userPerms 
        WHERE 
        	uT.id = 1
    Naturally it wont work since the data has commas in it.

    So what I am looking for in the output:
    Code:
        ID   | userPerms        | Name      | DOB
        ----------------------------------------
        5985 |Read,Upload,Admin |Bob Barker |12/12/1923
        895  |Read,Write        |Bill Gates |10/14/1955
        5897 |Read,Write,Admin  |Steve Jobs |02/24/1955
    Does anyone know how to split these out so that the inner join would then work as designed?

    UPDATE

    I got the needed data but I am unable to combine it into the 1 string?
    Code:
    SELECT 
    	uT.employeeID + '|' + uT.lastFirstMiddle + '|' + uT.ntName + '|' + 
    	uT.email + '|' + uT.firstName + '|' + uT.lastName + '|' + uT.active, 
    	(
    		SELECT 
    			',' + uP.type
    		FROM 
    			usersPermissions AS uP
    		WHERE 
    			',' + uT.userPerms + ',' 
    		LIKE 
    			'%,' + cast(uP.id AS nvarchar(20)) + ',%'
    		FOR 
    			XML PATH(''), TYPE
    	).value('substring(text()[1], 2)', 'varchar(max)') AS userPerms,
    	(
    		SELECT 
    			',' + uP.name
    		FROM 
    			pagePermissions AS uP
    		WHERE 
    			',' + uT.userPerms + ',' 
    		LIKE 
    			'%,' + cast(uP.id AS nvarchar(20)) + ',%'
    		FOR 
    			XML PATH(''), TYPE
    	).value('substring(text()[1], 2)', 'varchar(max)') AS userPermPages
    FROM 
    	usersTbl as uT 
    WHERE 
    	uT.id = '1';
    Last edited by StealthRT; 11-13-17 at 17:11.

Posting Permissions

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