SQL Server 2008 inner join with commas

Question

I am trying to figure out how to inner join a table that has ID's to my table that have all the ids in one row separated by commas.

The table with the commas is called (PA):

usersIDs
56488,51233,71055,98304,21577,12500

The table (SU) has the id and the name of the user:

UserID | Name
51233  | Bob Barker
21577  | Billy Knox
56488  | David Miller
etc etc

How can I inner join the name with the comma separated ID's in my first table?

 SELECT DISTINCT SU.Name, SU.UserID, SU.Enabled
 FROM Sec_User AS SU INNER JOIN
    Program_Access AS PA ON SU.UserId = PA.userIDS

Whenever you find it difficult to write seemingly simple SQL DML (e.g. queries) you should be looking for problems in SQL DDL (e.g. design flaws). The design flaw committed here is a first normal form (1NF) violation because the intersection of row and column is not a scalar value of the domain `UserID`. As the name suggests, 1NF is a fundamental requirement of the relational model on which SQL is based (albeit rather loosely). In short, SQL works best with relational data so I recommend you 'cure the disease' by fixing the data (the answers so far merely 'deal with the symptoms'). — onedaywhen, Mar 13 '12 at 10:11

Aaron Bertrand · Accepted Answer · 2012-03-13T00:13:02.133

Here's a way without a function:

SELECT DISTINCT SU.UserID, SU.Name, SU.Enabled
 FROM dbo.Sec_User AS SU
 INNER JOIN dbo.Program_Access AS PA
 ON ',' + PA.usersIDs + ',' LIKE '%,' + RTRIM(SU.UserID) + ',%';

But including the function for completeness (and because having such a function is very useful for many purposes). First create a table-valued function:

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( 
     SELECT Item = x.i.value('(./text())[1]', 'int') FROM ( 
       SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') 
       + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i)) AS y
     WHERE Item IS NOT NULL
   );

Now the JOIN:

 SELECT DISTINCT SU.UserID, SU.Name, SU.Enabled
  FROM dbo.Program_Access AS PA 
  CROSS APPLY dbo.SplitInts(PA.usersIDs, ',') AS s
  INNER JOIN dbo.Sec_User AS SU
  ON SU.UserID = s.Item;

score 1 · Answer 2 · answered Mar 13 '12 at 00:01

1) First of all, create split function. Example here.

2) Then, try to do like this:

DECLARE @PA TABLE
 ( usersIDs VARCHAR(50))

 INSERT INTO @PA(usersIDs) VALUES('56488,51233,71055,98304,21577,12500')

DECLARE @SU TABLE
(UserID INT, Name VARCHAR(50))

[enter link description here][2]INSERT INTO @SU(UserID, Name) VALUES (51233, 'Bob Barker')
INSERT INTO @SU(UserID, Name) VALUES (21577, 'Billy Knox')
INSERT INTO @SU(UserID, Name) VALUES (56488, 'David Miller')

SELECT * FROM @PA 
CROSS APPLY dbo.fnSplit(usersIDs,',')
LEFT JOIN @SU ON item = UserID

score 0 · Answer 3 · answered Mar 13 '12 at 00:13

According to this link How To Split A Comma Delimited String, you can have results like this:

NewPA_Table
RecID       UserID
1            56488
2            51233
3            71055
4            98304
5            21577
6            12500

and by generating that result, you could now join it to table SU.

SQL Server 2008 inner join with commas

3 Answers3