T-SQL query, multiple values in a field

Question

I have two tables in a database. The first table tblTracker contains many columns, but the column of particular interest is called siteAdmin and each row in that column can contain multiple loginIDs of 5 digits like 21457, 21456 or just one like 21444. The next table users contains columns like LoginID, fname, and lname.

What I would like to be able to do is take the loginIDs contained in tblTracker.siteAdmin and return fname + lname from users. I can successfully do this when there is only one loginID in the row such as 21444 but I cannot figure out how to do this when there is more than one like 21457, 21456.

Here is the SQL statement I use for when there is one loginID in that column

SELECT b.FName + '' '' + b.LName AS siteAdminName,
FROM tblTracker a
LEFT OUTER JOIN users b ON a.siteAdmin= b.Login_Id

However this doesn't work when it tries to join a siteAdmin with more than one LoginID in it

Thanks!

Answer 1

I prefer the number table approach to split a string in TSQL

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can now use a CROSS APPLY to split every row in your table like:

DECLARE @users table (LoginID int, fname varchar(5), lname varchar(5))
INSERT INTO @users VALUES (1, 'Sam', 'Jones')
INSERT INTO @users VALUES (2, 'Don', 'Smith')
INSERT INTO @users VALUES (3, 'Joe', 'Doe')
INSERT INTO @users VALUES (4, 'Tim', 'White')
INSERT INTO @users VALUES (5, 'Matt', 'Davis')
INSERT INTO @users VALUES (15,'Sue', 'Me')

DECLARE @tblTracker  table (RowID int, siteAdmin varchar(50))
INSERT INTO @tblTracker VALUES (1,'1,2,3')
INSERT INTO @tblTracker VALUES (2,'2,3,4')
INSERT INTO @tblTracker VALUES (3,'1,5')
INSERT INTO @tblTracker VALUES (4,'1')
INSERT INTO @tblTracker VALUES (5,'5')
INSERT INTO @tblTracker VALUES (6,'')
INSERT INTO @tblTracker VALUES (7,'8,9,10')
INSERT INTO @tblTracker VALUES (8,'1,15,3,4,5')

SELECT
    t.RowID, u.LoginID, u.fname+' '+u.lname AS YourAdmin
    FROM @tblTracker                                     t
        CROSS APPLY dbo.FN_ListToTable(',',t.siteAdmin)  st
        LEFT OUTER JOIN @users                           u ON st.ListValue=u.LoginID --to get all rows even if missing siteAdmin
        --INNER JOIN @users                                u ON st.ListValue=u.LoginID  --to remove rows without any siteAdmin
    ORDER BY t.RowID,u.fname,u.lname

OUTPUT:

RowID       LoginID     YourAdmin
----------- ----------- -----------
1           2           Don Smith
1           3           Joe Doe
1           1           Sam Jones
2           2           Don Smith
2           3           Joe Doe
2           4           Tim White
3           5           Matt Davis
3           1           Sam Jones
4           1           Sam Jones
5           5           Matt Davis
7           NULL        NULL
7           NULL        NULL
7           NULL        NULL
8           3           Joe Doe
8           5           Matt Davis
8           1           Sam Jones
8           15          Sue Me
8           4           Tim White

(18 row(s) affected)