Home SQL Server 2016 T-SQL convert comma string to inner join database name(s)
Reply: 3

SQL Server 2016 T-SQL convert comma string to inner join database name(s)

StealthRT
1#
StealthRT Published in 2017-11-13 20:00:54Z

I have a value for one of my columns that looks like this:

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:

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 won't work since the data has commas in it.

So what I am looking for in the output:

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 1

I got it working but:

It does not combine the userPerms into the original string

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';
Sentinel
2#
Sentinel Reply to 2017-11-13 20:10:41Z

Not great DB Design to a list of IDs like that. It's better to use a relation table to store the many to many relationship. That said, this might work:

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 ','+uT.userPerms+',' like '%,'+uP.id+',%'
WHERE 
    uT.id = 1

The idea is to add commas to the beginning and end of uT.userPerms and use the like operator to join to any occurance of uP.id (also surrounded with commas) in uT.userPerms.

Shnugo
3#
Shnugo Reply to 2017-11-14 16:07:47Z

Well, you asked me... :-)

I do not have an installation of SQL-Server 2016 working at the moment, so this is untested air code. But I think you need something like this:

SET DATEFORMAT mdy;

DECLARE @tblData TABLE(ID INT, userPerms VARCHAR(100),Name VARCHAR(100),DOB DATE);
INSERT INTO @tblData VALUES
 (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');

DECLARE @tblPerm TABLE(ID INT, Perm VARCHAR(100));
INSERT INTO @tblPerm VALUES
 (1,'Read')
,(2,'Write')
,(3,'Upload')
,(4,'Admin');

SELECT d.ID,d.Name,d.DOB 
      ,STRING_AGG(p.Perm,',') AS Perms
FROM @tblData AS d
CROSS APPLY STRING_SPLIT(d.userPerms,',') AS A
INNER JOIN @tblPerm AS p ON CAST(A.value AS INT)=p.ID --<-- added cast to int
GROUP BY d.ID,d.Name,d.DOB;

The function string_split() will separate the values at commas, which allows you to JOIN your catalog table. GROUP BY in combination with STRING_AGG() should return what you want. But I've never used this before...

Hope this helps!

UPDATE: A working solution without string-split and string-agg

SET DATEFORMAT mdy;

DECLARE @tblData TABLE(ID INT, userPerms VARCHAR(100),Name VARCHAR(100),DOB DATE);
INSERT INTO @tblData VALUES
 (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');

DECLARE @tblPerm TABLE(ID INT, Perm VARCHAR(100));
INSERT INTO @tblPerm VALUES
 (1,'Read')
,(2,'Write')
,(3,'Upload')
,(4,'Admin');

WITH Joined As
(
    SELECT d.ID
          ,d.Name
          ,d.DOB
          ,p.Perm 
    FROM @tblData AS d
    CROSS APPLY(SELECT CAST('<x>' +  REPLACE(d.userPerms,',','</x><x>') + '</x>' AS XML)) AS A(casted)
    CROSS APPLY A.casted.nodes(N'/x') AS B(numbers)
    INNER JOIN @tblPerm AS p ON CAST(B.numbers.value(N'text()[1]',N'int') AS INT)=p.ID
)
SELECT j.ID
      ,j.Name
      ,j.DOB 
      ,STUFF((
        SELECT ',' + x.perm
        FROM Joined AS x
        WHERE x.ID=j.ID
        FOR XML PATH('')
       ),1,1,'') AS Perm
FROM Joined AS j
GROUP BY ID,Name,DOB
sanjay singh
4#
sanjay singh Reply to 2017-11-16 13:14:38Z
create table  #tblData (ID INT, userPerms VARCHAR(100),Name VARCHAR(100),DOB DATE);
INSERT INTO #tbldata VALUES
 (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');

create table   #tblPerm (ID INT, Perm VARCHAR(100));
INSERT INTO #tblPerm VALUES
 (1,'Read')
,(2,'Write')
,(3,'Upload')
,(4,'Admin');    

Create FUNCTION [dbo].[fnSplitString] 
    ( 
        @string NVARCHAR(MAX), 
        @delimiter CHAR(1) 
    ) 
    RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
    ) 
    BEGIN 
        DECLARE @start INT, @end INT 
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
        WHILE @start < LEN(@string) + 1 BEGIN 
            IF @end = 0  
                SET @end = LEN(@string) + 1

            INSERT INTO @output (splitdata)  
            VALUES(SUBSTRING(@string, @start, @end - @start)) 
            SET @start = @end + 1 
            SET @end = CHARINDEX(@delimiter, @string, @start)

        END 
        RETURN 
    END
    go
    ;with cte
    as
    (
    select t1.ID,t1.userPerms,t1.Name,t3.Perm ,t1.DOB from #tblData as t1
    cross apply 
    dbo.fnsplitstring(t1.userPerms,',') as t2
    join #tblPerm as t3
    on t3.ID=t2.splitdata
    )

    select id,STUFF((select ','+perm from cte t1 
    where t1.ID=t2.id 
    for XML path('')),1,1,'')  as userperms,Name,DOB
    from cte t2
    group by id ,name,DOB
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.326797 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO