Results 1 to 2 of 2
\r\n \r\n\r\n \r\n \r\n
\r\n
\r\n
\r\n Hey all I have a value for one of my columns that looks like this:
\n
\r\n
Code:
\r\n
    ID   | userPerms | Name      | DOB\n    -' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-\n    5985 |1,3,4      |Bob Barker |12/12/1923\n    895  |1,2        |Bill Gates |10/14/1955\n    5897 |1,2,4      |Steve Jobs |02/24/1955
\r\n
That column being the userPerms column.
\n
\nI need to be able to Inner Join with the userPerm table associated with those numbers.
\n
\nMy query is currently:
\n
\r\n
Code:
\r\n
    SELECT \n    	uT.employeeID + \'|\' + uT.lastFirstMiddle + \'|\' + uT.ntName + \'|\' + uT.email + \'|\' + \n    	uT.firstName  + \'|\' + uT.lastName        + \'|\' + uT.active + \'|\' + uT.userPerms + \'|\' + \n        uT.userPermPages \n    FROM \n    	usersTbl AS uT\n    INNER JOIN \n    	usersPermissions AS uP\n    	ON uP.id = uT.userPerms \n    WHERE \n    	uT.id = 1
\r\n
Naturally it wont work since the data has commas in it.
\n
\nSo what I am looking for in the output:
\n
\r\n
Code:
\r\n
    ID   | userPerms        | Name      | DOB\n    -' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-\n    5985 |Read,Upload,Admin |Bob Barker |12/12/1923\n    895  |Read,Write        |Bill Gates |10/14/1955\n    5897 |Read,Write,Admin  |Steve Jobs |02/24/1955
\r\n
Does anyone know how to split these out so that the inner join would then work as designed?
\n
\nUPDATE
\n
\nI got the needed data but I am unable to combine it into the 1 string?
\n
\r\n
Code:
\r\n
SELECT \n	uT.employeeID + \'|\' + uT.lastFirstMiddle + \'|\' + uT.ntName + \'|\' + \n	uT.email + \'|\' + uT.firstName + \'|\' + uT.lastName + \'|\' + uT.active, \n	(\n		SELECT \n			\',\' + uP.type\n		FROM \n			usersPermissions AS uP\n		WHERE \n			\',\' + uT.userPerms + \',\' \n		LIKE \n			\'%,\' + cast(uP.id AS nvarchar(20)) + \',%\'\n		FOR \n			XML PATH(\'\'), TYPE\n	).value(\'substring(text()[1], 2)\', \'varchar(max)\') AS userPerms,\n	(\n		SELECT \n			\',\' + uP.name\n		FROM \n			pagePermissions AS uP\n		WHERE \n			\',\' + uT.userPerms + \',\' \n		LIKE \n			\'%,\' + cast(uP.id AS nvarchar(20)) + \',%\'\n		FOR \n			XML PATH(\'\'), TYPE\n	).value(\'substring(text()[1], 2)\', \'varchar(max)\') AS userPermPages\nFROM \n	usersTbl as uT \nWHERE \n	uT.id = \'1\';
\r\n
\r\n
\r\n
\r\n\r\n \r\n\r\n\r\n
\r\n \r\n \r\n
\r\n \r\n Last edited by StealthRT; 11-13-17 at 17:11.\r\n \r\n \r\n
\r\n \r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n
\r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n\r\n'; pd[6650276] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #2\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n Celko is offline\r\n\r\n \r\n \r\n Registered User\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Jan 2013
    \r\n \r\n \r\n
    Posts
    359
    \r\n \r\n
    \r\n \r\n
    Provided Answers: 1
    \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n

    \r\n Normalization is good\r\n

    \r\n \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n In the future, would you please post DDL along with sample data? It would also help if you read a book on basic data modeling so you know there\'s no such thing as generic ID or generic name; they have to be the identifier of something in particular and the name of something in particular. It would also help if you know the correct format in ANSI/ISO standard SQL for dates (since this is one of the most common IT standards on earth, so you should know it).
    \r\n
    \r\nThe first step is to post DDL, we can actually use
    \r\n
    \r\nCREATE TABLE Users
    \r\n(emp_id CHAR(5) NOT NULL PRIMARY KEY
    \r\n CHECK (emp_id LIKE \'[0-9][0-9][0-9][0-9][0-9]\'),
    \r\n user_name VARCHAR(35) NOT NULL birth_date DATE NOT NULL),
    \r\n user_permission CHAR(6) NOT NULL
    \r\n CHECK (user_permission IN (\'Read\',\'Upload\',\'Admin\',\'Write\'))
    \r\n);
    \r\n
    \r\nNow let\'s normalize it. That will involve splitting it into two separate tables.
    \r\n
    \r\nCREATE TABLE Users
    \r\n(emp_id CHAR(5) NOT NULL PRIMARY KEY
    \r\n CHECK (emp_id LIKE \'[0-9][0-9][0-9][0-9][0-9]\'),
    \r\n user_name VARCHAR(35) NOT NULL birth_date DATE NOT NULL)
    \r\n);
    \r\n
    \r\nCREATE TABLE User_Permissions
    \r\n(emp_id CHAR(5) NOT NULL
    \r\n REFERENCES Users(emp_id)
    \r\n ON DELETE CASCADE
    \r\n ON UPDATE CASCADE,
    \r\n user_permission CHAR(6) NOT NULL
    \r\n CHECK (user_permission IN (\'Read\',\'Upload\',\'Admin\',\'Write\')),
    \r\n PRIMARY KEY (emp_id, user_permission)
    \r\n);
    \r\n
    \r\nWhy not do this in the front end? You seem to have only four permissions and they can be modeled with short strings. Since an integer is eight bytes the strings are actually shorter!
    \r\n
    \r\n>> My query is currently.. Naturally it wont work since the data has commas in it. <<
    \r\n
    \r\nActually, you have more problems than that. The affix "TBL_" is a design flaw that mixes data and metadata to give you what we call a Tibble. Camel case is a measurable disaster that makes your eyes jump around to the first uppercase letter in each data element name and adds about 8% more to debugging times. There is no such thing as a generic "id"; it has to be the identifier of something in particular. The only display format for a date in ANSI/ISO standard SQL is ISO 8601 (yyyy-mm-dd), and not your local dialect. Finally, this is not COBOL, so we don\'t do string formatting in a query. In SQL. There\'s a presentation layer that arranges it for output and never in a query.
    \r\n
    \r\n>> So what I am looking for in the output: <<
    \r\n
    \r\nWhy do you hate normalization so much? Dr. Codd really liked it when he was building the relational model
    \r\n
    \r\nINSERT INTO Users
    \r\nVALUES (\'5985\', \'Bob Barker\', \'1923-12-12\');
    \r\n
    \r\nINSERT INTO User_Permissions
    \r\nVALUES
    \r\n (\'5985\', \'Read\'),
    \r\n (\'5985\', \'Upload\'),
    \r\n (\'5985\', \'Admin\');
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; // next/previous post info pn[6650227] = "6650276,6650276"; pn[0] = ",6650227"; pn[6650276] = "6650227,6650227"; // cached usernames pu[0] = guestphrase; pu[34265] = "StealthRT"; pu[308972] = "Celko"; // -->

    Threaded View

    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
    •