Results 1 to 2 of 2

Hybrid 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.

  2. #2
    Join Date
    Jan 2013
    Posts
    359
    Provided Answers: 1

    Normalization is good

    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).

    The first step is to post DDL, we can actually use

    CREATE TABLE Users
    (emp_id CHAR(5) NOT NULL PRIMARY KEY
    CHECK (emp_id LIKE '[0-9][0-9][0-9][0-9][0-9]'),
    user_name VARCHAR(35) NOT NULL birth_date DATE NOT NULL),
    user_permission CHAR(6) NOT NULL
    CHECK (user_permission IN ('Read','Upload','Admin','Write'))
    );

    Now let's normalize it. That will involve splitting it into two separate tables.

    CREATE TABLE Users
    (emp_id CHAR(5) NOT NULL PRIMARY KEY
    CHECK (emp_id LIKE '[0-9][0-9][0-9][0-9][0-9]'),
    user_name VARCHAR(35) NOT NULL birth_date DATE NOT NULL)
    );

    CREATE TABLE User_Permissions
    (emp_id CHAR(5) NOT NULL
    REFERENCES Users(emp_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    user_permission CHAR(6) NOT NULL
    CHECK (user_permission IN ('Read','Upload','Admin','Write')),
    PRIMARY KEY (emp_id, user_permission)
    );

    Why 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!

    >> My query is currently.. Naturally it wont work since the data has commas in it. <<

    Actually, 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.

    >> So what I am looking for in the output: <<

    Why do you hate normalization so much? Dr. Codd really liked it when he was building the relational model

    INSERT INTO Users
    VALUES ('5985', 'Bob Barker', '1923-12-12');

    INSERT INTO User_Permissions
    VALUES
    ('5985', 'Read'),
    ('5985', 'Upload'),
    ('5985', 'Admin');

Posting Permissions

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